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))



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors