Skip to Content
avatar image
Former Member

YTD and MTD calcs

Hi Team,

I have a reporting period data in format 201601,201602,201603 etc and 201501,etc

I don't have Year,quarter,month in my data

I have to calculate YTD and MTD calcs, and reporting period is a prompt so based on the latest reporting period

i have to get the values

I have created calcs for CurrentYear MTD and YTD but for previous years

i am facing an issue

Below calc is for MTD, YTD i found a way nd fixed it but still looking for full proof soln :-)

CurrentYear calc: I am first getting the maximum reporting period value and based on that doing calcs

=(([Revenue] Where (Left([Reporting Period];4) =

Left(FormatNumber(Max(ToNumber([Reporting Period]));"0");4)ForAll([Reporting Period])

And (Right([Reporting Period];2)) <= (Right(FormatNumber(Max(ToNumber([Reporting Period]));"0");2))

ForAll([Reporting Period])))

)

PreviousYear calc: i am just subtracting the year by -1 to get previous year

=(([Revenue] Where (Left([Reporting Period];4) =

Left(FormatNumber(Max(ToNumber([Reporting Period]))-1;"0");4)ForAll([Reporting Period])

And (Right([Reporting Period];2)) <= (Right(FormatNumber(Max(ToNumber([Reporting Period]));"0");2))

ForAll([Reporting Period])))

)

The issue is now i am getting same values for both Previous and Current years

any other ideas or suggestions?

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Apr 05, 2016 at 01:12 PM

    Hi All,

    My bad!! 😔

    I was doing some calc mistake

    for answer

    PreviousYear calcs are

    =(([Revenue] Where (Left([Reporting Period];4) =

    Left(FormatNumber((Max(ToNumber(Left([Reporting Period];4)))-1);"0");4)ForAll([Reporting Period])

    And (Right([Reporting Period];2)) <= (Right(FormatNumber(Max(ToNumber([Reporting Period]));"0");2))ForAll([Reporting Period])

    ))

    )

    I was subtracting wrong values!!

    Add comment
    10|10000 characters needed characters exceeded