Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Not applicable

Can't filter my 12 month rolling average

Hi! I am trying to get the 12 month rolling avereage into a chart showing a selected date and the 12 previous months, I get my rollling average from a measure that gets the headcount for the last 24 months (it could be 12 instead, but by doing that, I wont be able to get the 12 month rolling average for the first month on the chart).


My problem is, whenever I try to apply date filters to the rolling average measure, it won't show what I need and the chart also shows months forward my selected date (let say I just want to see between May 19 and May 20, but the rolling average keep showing data for future months.


these are the 12 month headcount measure and the rolling average measure:


HC ALL 12M = 
VAR CurrentDate = MAX('DistinctDate'[EOM Date])
VAR PreviousDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,day(CurrentDate))
VAR Result =
    SUM('Staff Metrics BI'[All Headcount]),
    FILTER('Staff Metrics BI','Staff Metrics BI'[EOM Date] > PreviousDate && 'Staff Metrics BI'[EOM Date] <= CurrentDate)


HC ALL 24M rolling average = 
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __LAST_DATE = ENDOFMONTH('Calendar'[Date].[Date])
			CALCULATE([HC ALL 24M], ALL('Calendar'[Date].[Day]))

  and this is the result of the measures, and I can't make the rolling average to only show values between the highlighted area, based on EOM Date. If anyone can give me an idea on how to filter my rolling average would be great, because I will use it as base of other calculations.




Not applicable


// First, create the basic measure:

[HC] = SUM( 'Staff Metrics BI'[All Headcount] )

// Then...

[HC 12M RA] = // HC 12-Month Rolling Average
// Please bear in mind that if there are not
// enough months to go back, you CANNOT calculate
// this measure and as such this measure will
// return BLANK. Also, if you select a period
// of time that has a non-empty intersection with
// the future, the measure will return BLANK. And
// please do yourself a favour and stop using the
// automatically generated datetime hierarchies.
// This not only makes the model bigger but also
// hides traps that you'll easily fall into without
// even realizing. This feature, as the documentation
// says, is for the simplest of models and should
// not be used in professional development. Thanks.
// Change the variable's value to 24 for [HC 24M RA]
// or, alternatively, create a disconnected table that
// you'll use to harvest the value of how many months
// to go back, thus making the measure fully dynamic.
var __numOfMonthsToGoBack = 12
var __today = TODAY()
VAR __lastDateInScope = MAX( 'Calendar'[Date] )
var __periodToCalcOver =
        // Calendar must be a date table
        // in the model marked as such.
        (-1) * __numOfMonthsToGoBack,
var __firstDateInPeriodToCalcOver =
var __enoughDaysToCalculate =
    // We have to make sure that we CAN in fact
    // go back that many months in the calendar
    // and we won't fall off of its left edge.
        = EDATE(
            (-1) * __numOfMonthsToGoBack 
        ) + 1 // need to add 1 day to the shifted date
var __shouldCalc =
    __lastDateInScope <= __today
    && __enoughDaysToCalculate
var __result =
    if( __shouldCalc,
            // This is the monthly average.
            // If you want a daily average,
            // you have to divide by the number
            // of days in __periodToCalcOver.


Super User
Super User

@Anonymous , have tried this kind of formula for rolling



Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
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))


Rolling 24 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-24,MONTH))
Rolling 24 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-24,MONTH))
Rolling 24 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-24,MONTH))

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors