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

Frequent Visitor

## Find rolling 12 month average trend

I have a custom measure named 'Measure_2' that evaluates and generates values to common unit. I have data ranging from 2018 to 2023.

I would like to create a new DAX measure '12MonthRollingAvg' that will find the rolling average of past 12 months and another DAX measure 'Past12monthRollingAvg' that will find rolling average of 12 months a year before than current date.

I tried this DAX command but it fails.

12MonthRollingAvg =
VAR numOfMonths=12
VAR lastCurrentDate=MAX('Calendar_Month'[Calendar Month - Number])
VAR Period = DATESINPERIOD('Calendar_Month'[Calendar Month - Name], lastCurrentDate,-numOfMonths,MONTH)
VAR result = CALCULATE(
AVERAGEX(
VALUES('Calendar_Month'[Calendar Month - Number]), [Measure_2]),
Period
)
Return
if ([Measure_2]>0 , result)

My desired visualization should look something like this:

The blue line is for Measure '12MonthRollingAvg' and grey line is 'Past12MonthRollingAvg'

My table for dates looks like this:

2 REPLIES 2
Super User

Hi,

Try these kinds of structures:

12M AVG =

`CALCULATE ( DIVIDE ( selectedmeasure(), 12 ), DATESBETWEEN ( 'Calendar'[Date], DATEADD ( LASTDATE ( DATEADD ( 'Calendar'[Date], -12, MONTH ) ), +1, DAY ), LASTDATE ( 'Calendar'[Date] ) ) )`

PY 12M =

`CALCULATE ( [12M AVG], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )`

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Proud to be a Super User!

Frequent Visitor

Thanks for your comment. I tried out your DAX expression and tried to visualize it using spark lines. But the output isnt what I expected. I had to change your expression to accomodate my tables. This is how the DAX expression looks like now:

Rolling12Month = CALCULATE ( DIVIDE ( selectedmeasure(), 12 ), DATESBETWEEN ( 'Calendar_Month'[Calendar Month - Number], DATEADD ( LASTDATE ( DATEADD ( 'Calendar_Month'[Calendar Month - Number], -12, MONTH ) ), +1, DAY ), LASTDATE ( 'Calendar_Month'[Calendar Month - Number] ) ) )

However, the visualization with this still gives an error.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.