Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jvandyck
Helper IV
Helper IV

rolling 12 month total

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 Q1Jan613867537061386753.7
2019 Q1Feb6162159484123008348.5
2019 Q1Mar6169687878184705227.3
2019 Q2Apr 184705227.3
2019 Q2May 184705227.3
2019 Q2Jun 184705227.3
2019 Q3Jul 184705227.3
2019 Q3Aug 184705227.3
2019 Q3Sep 184705227.3
2019 Q4Oct 184705227.3
2019 Q4Nov 184705227.3
2019 Q4Dec 184705227.3
2020 Q1Jan9061835891123318473.6
2020 Q1Feb905593617161696878.78
2020 Q1Mar9132986821 
2020 Q2Apr9158688579 
2020 Q2May9170223979 
2020 Q2Jun9167155460 
2020 Q3Jul9925782408 
2020 Q3Aug9248986131 
2020 Q3Sep9176523207 
2020 Q4Oct9093881567 
2020 Q4Nov9055676685 
2020 Q4Dec8991387446 

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

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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.

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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.

Thank you for your extremely quick answer!

Thanks for you extremely quick answer!!!!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors