cancel
Showing results 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

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

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

1 ACCEPTED SOLUTION
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.

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

Helper IV

Helper IV

Thanks for you extremely quick answer!!!!

Announcements

#### Power BI Monthly Update - July 2024

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

Top Solution Authors
Top Kudoed Authors