cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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?

 

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 
DateAccountingPeriod
1..31201801
1..28201802
1..31201803
1..30

201804

 

Employees 
IDFromDateToDate
12018-01-019999-12-31
22018-01-019999-12-31
32018-03-019999-12-31
42018-04-019999-12-31

 

Wanted result 
AccountingperiodHeadcountAverage 3 month
20180122,0
20180222,0
20180332,3
20180443,0
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
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 =
    ADDCOLUMNS (
        TOPN (
            3,
            FILTER (
                ALL ( Calender[AccountingPeriod] ),
                Calender[AccountingPeriod] <= CurrentPeriod
            ),
            [AccountingPeriod], DESC
        ),
        "HeadcountLast3", [Headcount]
    )
RETURN
    AVERAGEX ( Last3Periods, [HeadcountLast3] )


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

4 REPLIES 4
Highlighted
Solution Sage
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 ?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Highlighted
Super User II
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 =
    ADDCOLUMNS (
        TOPN (
            3,
            FILTER (
                ALL ( Calender[AccountingPeriod] ),
                Calender[AccountingPeriod] <= CurrentPeriod
            ),
            [AccountingPeriod], DESC
        ),
        "HeadcountLast3", [Headcount]
    )
RETURN
    AVERAGEX ( Last3Periods, [HeadcountLast3] )


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

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 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

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

Upcoming Events

Experience what’s next for Power BI

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors