Rolling Headcount with by Adding coming Month

[adyaan]

Dear Team,

Thanks for giving us a wonderful tool 🙂

I am facing the issue while measuring the Rolling headcounts months wise (by Adding one month two second then getting average of second so on till 12 months or end date of filter )

Please refer the below table Actual Headcount the 12 Month Rolling headcount by taking simple average and when putting the measure on powerbi it is not giving expected result for ref. col. Rolling 12 Months  PowerBI . Want to achieve it by Start date and end date filter condition. The powerbi formula is placed here. 

Request you, please look and share the solution. 

Thanks in Advance.

 YTD Headcount (Rolling Avg 12-Months) =

VAR NumOfMonths = 12

VAR LastCurrentDate =

    MAX ( Dates[Date] )

VAR Period =

    DATESINPERIOD ( Dates[Date], LastCurrentDate, - NumOfMonths, MONTH )

VAR Result =

    CALCULATE (

        AVERAGEX (

            VALUES ( Dates[Year Month] ),

            [Headcount]

        ),

        Period

    )

VAR FirstDateInPeriod = MINX ( Period, Dates[Date] )

VAR LastDateWithSales = MAX ( Dates[Date] )

RETURN

    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

Date Filter	From	01-Apr-22	To	31-Mar-23
Month-Name	Headcount	12-M Rolling Excel	Rolling 12 Months  PowerBI	diff
Apr-22	5322	5322	5244	78
May-22	5325	5324	5254	69
Jun-22	5357	5335	5266	68
Jul-22	5400	5351	5282	69
Aug-22	5429	5367	5299	68
Sep-22	5489	5387	5322	65
Oct-22	5470	5399	5344	55
Nov-22	5534	5416	5372	44
Dec-22	5602	5436	5403	33
Jan-23	5637	5457	5433	23
Feb-23	5665	5475	5464	12
Mar-23	5660	5491	5491	0
	5490.833333	5396.550511	5347.895833

[amitchandak · Accepted solution]

@adyaan , Try one of the two

Rolling 12 Sales =
var _max = max(date[date]) // or today()
var _min = Max( eomonoth(_max,-12)+1 , minx(allselected(date), Date[Date]))
return
CALCULATE(AVerageX(Values(date[Month year]) , calculate(SUM(Sales[Sales Amount]))),filter(allselected(date), date[date] <=_max && date[date] >=_min))

or

Rolling 12 Sales =
var _max = max(date[date]) // or today()
var _min = Max( eomonoth(_max,-12)+1 , minx(allselected(date), Date[Date]))
return
CALCULATE(AVerageX(Values(date[Month year]) , calculate(SUM(Sales[Sales Amount]))),filter(all(date), date[date] <=_max && date[date] >=_min))

[adyaan]

Thank you so much Amit 

It is great help, it works well 

It works well for me