Rolling 12 Months DAX Measure in Power BI

In less than four minutes, this video shows you how to create a rolling 12 months DAX measure. It assumes a working understanding of the Power BI Filter Context and that you have already set up and are using a Dates dimension table in your model.

The DAX example below defines a MinDate and the MaxDate value for the 12 months up to and including the selected date:

Var SelectedMaxDate = MAX ( Dates[Date] )
Var MinDate =
CALCULATE (
MIN ( Dates[Date] ),
FILTER (
ALL ( Dates ),
DATEADD (
Dates[Date],
1,
YEAR
) "Greaterthan" SelectedMaxDate
)
)
Return
CALCULATE (
SUM ( Sales[Amount] ),
ALL ( Dates ),
Dates[Date] "LessThanOrEqualTo" SelectedMaxDate,
Dates[Date] "GreaterThanOrEqualTo" MinDate
)

Sorry, I had to use "GreaterThan" and ""LessThanOrEqualTo" and "GreaterThanOrEqualTo" because YouTube won't let me put the actual symbols, but I am sure you know what I mean :)

[Pandamaske]

Thank you so much for this well detailed video, and for the code ! You saved me !!

[chintalasudhaker]

is it possible to show rolling current13 months data and compare with rolling prior year months data? example August 2023 to August 2022 and August 2022 to August 2021.

[idolsensei]

what if in the same sheet I have a visual that needs to show the normal total or values only of a certain month? How can I do that using 1 month slicer only and with the 12 months roll visual as well?

[orangutanklaus]

Thank you so much!! Exactly what I was looking for!!

[Mohammed-khlj]

Hi, i am working on simillar thing where i am plotting data for 13months say from May 2023 to May 2024 but i am unable to sort it in proper order where May 2023 should be the first and May 2024 should come last. Any help would be great

[pgadam]

In the FILTER function on line 6, we are using a different table 'Dates' than the table used in the first argument on the CALCULATE function 'Sales'. My question is how can you apply the filter on a different table if your are doing the calculation on another table? Does it imply that both the tables are already joined someway? Hope it makes sense.

[ferlandordie]

Could you create a measure for the MinDate variable? I would like to reuse it within many other measures. That would lighten my code.

[leticiacardoso]

How can I do the rolling 12 to show a month with zero values for example? In my graphs it works but skips the months with zero values.

[pierre-lucvachon]

Struggling to do the same thing for another column that show the Rolling 12 months of the last year ( i want to compare both current rolling 12 months vs last rolling 12 months)

Note : based on my slicer selection

[santiagoaviles]

Podria servir la version sage 50 us estos dashboard?

[kathrynhaycock]

I used the Date Hierarchy field as my date calculation which hasn't given me the option to have a relative date filter type. Does that matter? I tried adding Year in the Legend and last year works great but this year has completely flipped and goes in the wrong direction! Ideally I would have them both mapped side-by-side to see the difference

[niclasnygren]

Would it be possible for you to post the DAX in the info? Thank you!

[JonasArnout]

i think this is cumulative ... you would need a 2nd data table to fix this properly i think. 1 for selecting the evaluation date and one for displaying the results in a visual. the first date table does not have any relationships and the selection must be introduced in every measure, the second date table is a normal date table with relationships to the fact tables

[Sophia-nbgt]

No sure why I could not get any result, I just changed the column name