Average employee count previous 12 months

[Bfaws]

Hello, 

I hope someone can help.  I have a measure below which looks at adding the employee headcount for each month in the previous 12 months and gives an average.  I`m trying figure out another measure which will give the result below but at the previous month or if I add a filter the previous 12 months at a specific time.  For example January 2021 would be previous 12 months from January 2021, and another to show December 2020 which would show previous 12 months starting from December.  This wouls allow me to compare two months to see if the overall employee average number has increased or decreased.

Thanks in advance for any help.

Brendan

Av. Employees = 
var __periodInQuestion = 
    DATESINPERIOD(
        Rolling_Calendar_Lookup[Date],
        MAX( Rolling_Calendar_Lookup[Date] ),
        -12, 
        MONTH
    )
var __result =
    AVERAGEX(
        __periodInQuestion,
        [Total FTE]
    )
return
    __result

[amitchandak]

@Bfaws , Try like

AvgPrv12Months =
divide( CALCULATE([Total FTE],DATESINPERIOD('DateTable'[Date ],MAX('DateTable'[Date]),-12,MONTH)) ,
CALCULATE(distinctCOUNT('DateTable'[Month Year]),DATESINPERIOD('DateTable'[Date],MAX('DateTable'[Date]),-12,MONTH), filter(Table, not(isblank([Toatl FTE])))))

[Bfaws]

@amitchandak 

Thanks for the reply.

I`ve not been able to replicate this.  I`ve ammended the measure above to suit my date table but not sure about highlighted part of measure - you refer to Table but i`m not sure what you mean here. Thanks.

AvgPrv12Months =
divide( CALCULATE([Total FTE],DATESINPERIOD('DateTable'[Date ],MAX('DateTable'[Date]),-12,MONTH)) ,
CALCULATE(distinctCOUNT('DateTable'[Month Year]),DATESINPERIOD('DateTable'[Date],MAX('DateTable'[Date]),-12,MONTH), filter(Table, not(isblank([Toatl FTE])))))

[v-rzhou-msft · Accepted solution]

Hi @Bfaws 

I think you want to compare two values.

The first one is the average of the sum of  headcount in rolling 12 months before the month you select by month.

The second one is the average of the sum of headcount in rolling 12 months before the month you select -1 by month.

Due to I don't know your data model, I build a sample to have a test.

I add a YearMonth column for calculating.

YearMonth = YEAR(Sheet46[Date])*100+MONTH(Sheet46[Date])

Date Table:

Rolling_Calendar_Lookup = CALENDARAUTO()

AvSel = 
VAR _Sel = MAX(Rolling_Calendar_Lookup[Date])
VAR _Sel_Max = EOMONTH(_Sel,0)
VAR _Sel_Min = EOMONTH(_Sel,-12)+1
VAR _Total = SUMX(FILTER(ALL(Sheet46),Sheet46[Date]>=_Sel_Min&&Sheet46[Date]<=_Sel_Max),Sheet46[Value])
VAR _CountMonth = CALCULATE(DISTINCTCOUNT(Sheet46[YearMonth]),FILTER(ALL(Sheet46),Sheet46[Date]>=_Sel_Min&&Sheet46[Date]<=_Sel_Max))
Return
DIVIDE(_Total,_CountMonth)

AvSelLastMonth = 
VAR _Sel = MAX(Rolling_Calendar_Lookup[Date])
VAR _Sel_Max = EOMONTH(_Sel,-1)
VAR _Sel_Min = EOMONTH(_Sel_Max,-12)+1
VAR _Total = SUMX(FILTER(ALL(Sheet46),Sheet46[Date]>=_Sel_Min&&Sheet46[Date]<=_Sel_Max),Sheet46[Value])
VAR _CountMonth = CALCULATE(DISTINCTCOUNT(Sheet46[YearMonth]),FILTER(ALL(Sheet46),Sheet46[Date]>=_Sel_Min&&Sheet46[Date]<=_Sel_Max))
Return
DIVIDE(_Total,_CountMonth)

Result is as below.

130/12 = 10.83 is the average of sum of headcount ranged by (2019/02/01-2020/01/31 the rolling 12 month)

121/12 = 10.08 is the average of sum of headcount ranged by (2019/01/01-2019/12/31 the rolling 12 month)

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Bfaws]

This is great and replicated exactly as expected. 

Many thanks.

Brendan