24 rolling Months from YYYYMM format

[jaymerry]

Hello all,

I'm trying to calculate directly and dynamically into a chart the 24 rolling Months.

My time dimension has the following format : YYYYMM.

Have you ever calculated this kind of dimension ?

I tried to use the "Makedate" function, without success.

Thank you for your help,

Best Regards,

Jaymerry

[jagan]

Hi,

If you have date field in your datamodel, then use the following expression

=Sum({<YearDimension=, MonthDimension=, QuarterDimension=, DateDimension={'>=$(=MonthStart(Max(DateDimension), -23))<=$(=MonthEnd(Max(DateDimension)))'}>} MeasureName)

Exclude Year, Month, Quarter dimensions in the expression as in the above expression.

Hope it helps you.

Regards,

Jagan.

[jagan]

Hi,

You can arrive a date by using MakeDate() as below

=MakeDate(Left(DateDimension, 4), Num(Right(DateDimension, 2)), 1)

Regards,

Jagan.

[jaymerry · Accepted solution]

Thank you Jagan,

I have found the following way, which works in my chart :

YEAR(addmonths(Makedate(left(%YYYYMM,4),right(%YYYYMM,2)),-24,1))&right(left(addmonths(Makedate(left(%YYYYMM,4),right(%YYYYMM,2)),-24,1),5),2)

Best Regards,

Jaymerry

[Gysbert_Wassenaar]

Read this document: Calculating rolling n-period totals, averages or other aggregations