Calculate rolling 12

[Anonymous]

Good morning,

How can I calculate rolling 12 without using a calendar table, but using the date field that comes from the database which has the format 01012021 for example.

Thanks.

[Anonymous · Accepted solution]

Hi @Anonymous 

I think you can build a number type YearMonth column and a Rank column to calcualte rolling 12.

YearMonth and Rank columns are calculated columns.

YearMonth = RIGHT('Sample'[Date],4)*100+LEFT('Sample'[Date],2)

RANK = RANKX('Sample',[YearMonth],,ASC,Dense)

Rolling 12 measure:

Rolling 12 = 
CALCULATE(SUM('Sample'[Value]),FILTER(ALL('Sample'),'Sample'[RANK]>MAX('Sample'[RANK])-12&&'Sample'[RANK]<=MAX('Sample'[RANK])))

Result:

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

It will be ideal to convert 01012020 to a proper date, then build a Calendar Table and then use the Date and Time Intelligence functions to get your desired result.

[ryan_mayu]

@Anonymous 

you need to change the format to date type. and use DATEADD

https://docs.microsoft.com/en-us/dax/dateadd-function-dax