Average employees rolling 3 months

[Markando]

Hi!

I got tables as below and using this dax to calculate the nr of employees in current month. However I can't get my head around how I should calculate the average on the last three months. Any good ideas?

Headcount =
VAR currentDate=IF(max(Calender[Date])>TODAY();TODAY();max(Calender[Date]))

Return

CALCULATE(
countrows(Employees);
FILTER(Employees;
Employees[FromDate]<= currentDate&&
Employees[ToDate]>= currentDate)
)

Tables:

Calender
Date	AccountingPeriod
1..31	201801
1..28	201802
1..31	201803
1..30	201804

Employees
ID	FromDate	ToDate
1	2018-01-01	9999-12-31
2	2018-01-01	9999-12-31
3	2018-03-01	9999-12-31
4	2018-04-01	9999-12-31

Wanted result
Accountingperiod	Headcount	Average 3 month
201801	2	2,0
201802	2	2,0
201803	3	2,3
201804	4	3,0

[Stachu · Accepted solution]

try this code - it uses TOPN to get last 3 months, ADDCOLUMN to add [Headcount] and finally AVERAGEX to calculate the average on the output

Average 3 month =
VAR CurrentPeriod =
    MAX ( Calender[AccountingPeriod] )
VAR Last3Periods =
    ADDCOLUMNS (
        TOPN (
            3,
            FILTER (
                ALL ( Calender[AccountingPeriod] ),
                Calender[AccountingPeriod] <= CurrentPeriod
            ),
            [AccountingPeriod], DESC
        ),
        "HeadcountLast3", [Headcount]
    )
RETURN
    AVERAGEX ( Last3Periods, [HeadcountLast3] )

[Markando]

Thanks! Beautiful piece of code that worked perfectly 

[LivioLanzo]

What is the From Date  and To Date the period in which employees stay in the company? are there cases when this is not in 9999 ?

do you want to perform the analysis only at month level ?

[Markando]

Hi

The fromDate and ToDate is the employment period. When ToDate is 9999 that means the employee is still active. If the employee had left the company it could say 2018-03-31 for example, then he shouldnt be added to the monthly value in april (but still two active months to the average)