rolling 12 month total

[jvandyck]

Hi 

I am not able to figure this one out. I am trying to create a rolling 12 month total and I have a formula like this:

Value 12 rolling =

Var _MaxDate=max('Date'[Date])

Return

calculate(sum('Key Figures'[Value]),filter(all('Date'[Date]),and('Date'[Date]<=_MaxDate,dateadd('Date'[Date],1,YEAR)>_MaxDate)))

However, when I create a table it does not seem to work:

YearQuarterMonthValueValue 12 rolling

2019	Q1	Jan	6138675370	61386753.7
2019	Q1	Feb	6162159484	123008348.5
2019	Q1	Mar	6169687878	184705227.3
2019	Q2	Apr		184705227.3
2019	Q2	May		184705227.3
2019	Q2	Jun		184705227.3
2019	Q3	Jul		184705227.3
2019	Q3	Aug		184705227.3
2019	Q3	Sep		184705227.3
2019	Q4	Oct		184705227.3
2019	Q4	Nov		184705227.3
2019	Q4	Dec		184705227.3
2020	Q1	Jan	9061835891	123318473.6
2020	Q1	Feb	9055936171	61696878.78
2020	Q1	Mar	9132986821
2020	Q2	Apr	9158688579
2020	Q2	May	9170223979
2020	Q2	Jun	9167155460
2020	Q3	Jul	9925782408
2020	Q3	Aug	9248986131
2020	Q3	Sep	9176523207
2020	Q4	Oct	9093881567
2020	Q4	Nov	9055676685
2020	Q4	Dec	8991387446

It is working for the 2019 data, but it does not add the 2020 data....

What is wrong with my formula?

The date table is defined as such in my model and is linked to the fact table...

[amitchandak · Accepted solution]

@jvandyck , Try with help from date table , using examples

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[jvandyck]

Thank you for your extremely quick answer!

[jvandyck]

Thanks for you extremely quick answer!!!!