Rolling 12 month average based on record created date

[pete.langlois]

I have attached a 4-column excel table as my dataset. I’d like to create a rolling 12 month average in which I add up everything in the Unit column that falls between the max date (4/1/2022) and 1 year prior (5/1/2021). Then I will divide this by 12.

The total units for this period should be 3643 and the 12 month average should be 304.

I was able to get the number I’m looking for by creating a measure that sums the units column, then using a date slicer to individually select 12 months back (May 2021 until April 2022 - see attached screenshot).

I’d like to recreate this result using DAX within a measure, since the max date and count of units will be constantly changing as new records are added to the dataset.

Hopefully, this makes sense and has a simple solution! I included both the dataset as well as a screenshot of how I was able to get the result I’m looking for by applying year and month filters.

rolling 12 month example.csv (148.3 KB)

[Harsh]

Hello @pete.langlois,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the condition that you’ve specified. I added an active date table and other one is passive. Below is the screenshot of the data model provided for the reference -

Now, I created couple of measures as provided below -

1). Calculation of Rolling Units Last 12 Months -

Rolling Units Last 12 Months = 
VAR _Reference_Date = 
MAX( Dates[Date] )

VAR _Previous_Dates = 
DATESINPERIOD(
    'Previous Dates'[Date] , 
    _Reference_Date , 
    -12 , 
    MONTH )

VAR _Results = 
CALCULATE( SUM( Data[Units] ) , 
    REMOVEFILTERS( Dates ) , 
    KEEPFILTERS( _Previous_Dates ) ,
    USERELATIONSHIP( Dates[Date] ,'Previous Dates'[Date] ) )

RETURN
_Results

2). Calculation of Average Units Last 12 Months -

Average Units Last 12 Months = 
DIVIDE( [Rolling Units Last 12 Months] , 12 , 0 )

Below is the screenshot of the final results provided for the reference -

I’m also attaching the working of the PBIX file for your perusal.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Rolling Average Last 12 Months - Harsh.pbix (156.8 KB)

[pete.langlois]

Thank you for your help!

Will this work without the year and month slicer? Or does the user have to select a month?

The report I’m working on doesn’t contain date slicers so I was hoping the measure would simply work from the latest date in the date column.

If we have to add a slicer so be it, but I was hoping we could avoid this.

Thank you,
Pete