Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.