cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Average employees rolling 3 months

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?

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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II

## Re: Average employees rolling 3 months

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 =
TOPN (
3,
FILTER (
ALL ( Calender[AccountingPeriod] ),
Calender[AccountingPeriod] <= CurrentPeriod
),
[AccountingPeriod], DESC
),
)
RETURN

Thank you for the kudos 🙂

Proud to be a Super User!

4 REPLIES 4
Highlighted
Solution Sage

## Re: Average employees rolling 3 months

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 ?

Proud to be a Datanaut!

Highlighted
Super User II

## Re: Average employees rolling 3 months

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 =
TOPN (
3,
FILTER (
ALL ( Calender[AccountingPeriod] ),
Calender[AccountingPeriod] <= CurrentPeriod
),
[AccountingPeriod], DESC
),
)
RETURN

Thank you for the kudos 🙂

Proud to be a Super User!

Highlighted
Frequent Visitor

## Re: Average employees rolling 3 months

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)

Highlighted
Frequent Visitor

## Re: Average employees rolling 3 months

Thanks! Beautiful piece of code that worked perfectly

Announcements

#### Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021