YTD and MTD calcs

[Former Member]

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?

[Former Member · Accepted solution]

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!!