cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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
Highlighted
Super User IV
Super User IV

@pipebritop , 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))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Solution Sage
Solution Sage

 

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

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors