cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
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 =
CALCULATE(
    SUM('Staff Metrics BI'[All Headcount]),
    FILTER('Staff Metrics BI','Staff Metrics BI'[EOM Date] > PreviousDate && 'Staff Metrics BI'[EOM Date] <= CurrentDate)
)
RETURN
Result

 

HC ALL 24M rolling average = 
IF(
	ISFILTERED('Calendar'[Date]),
	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])
	VAR __DATE_PERIOD =
		DATESBETWEEN(
			'Calendar'[Date].[Date],
			STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)),
			ENDOFMONTH(DATEADD(__LAST_DATE, 0, MONTH))
		)
	RETURN
		AVERAGEX(
			CALCULATETABLE(
				SUMMARIZE(
					VALUES('Calendar'),
					'Calendar'[Date].[Year],
					'Calendar'[Date].[QuarterNo],
					'Calendar'[Date].[Quarter],
					'Calendar'[Date].[MonthNo],
					'Calendar'[Date].[Month]
				),
				__DATE_PERIOD
			),
			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.

Capture.PNG

 

THanks!

2 REPLIES 2
Anonymous
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 =
    DATESINPERIOD(
        // Calendar must be a date table
        // in the model marked as such.
        'Calendar'[Date],
        __lastDateInScope,
        (-1) * __numOfMonthsToGoBack,
        MONTH
    )
var __firstDateInPeriodToCalcOver =
    MINX(
        __periodToCalcOver,
        'Calendar'[Date]
    )
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.
    __firstDateInPeriodToCalcOver
        = EDATE(
            __lastDateInScope,
            (-1) * __numOfMonthsToGoBack 
        ) + 1 // need to add 1 day to the shifted date
var __shouldCalc =
    __lastDateInScope <= __today
    && __enoughDaysToCalculate
var __result =
    if( __shouldCalc,
        CALCULATE(
            // This is the monthly average.
            // If you want a daily average,
            // you have to divide by the number
            // of days in __periodToCalcOver.
            [HC]/__numOfMonthsToGoBack,
            __periodToCalcOver
        )
    )
return
    __result

 

amitchandak
Super User
Super User

@Anonymous , have tried this kind of formula for rolling

 

example


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

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors