Skip to Content 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

### 1 Answer

• Best Answer 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