cancel
Showing results for
Did you mean: Frequent Visitor

## Calculation on headcount and leavers_moving average

Hello everyone,

Thank you for viewing my post.

My question is how do I calculate number of leavers between period Oct 2021 to Sept 2022 divided by ((starting headcount e.g. Oct 2021 + ending headcount Sept 2022)/2)?
And then,  number of leavers between period Nov 2021 to Oct 2022 divided by ((starting headcount e.g. Nov 2021 + ending headcount Oct 2022)/2), etc?

I have the following data:

Starting headcount (number of employees at the start of the month), ending headcount (number of employees end of the month), number of leavers- they are all separate tables.

Starting headcount (Month/Year), Ending headcount (Month/Year)and Leavers(Termination start of month (Month/Year) are linked to the Calendar (Month/Year) table.
Nov 2021: 6800

Oct 2022: 5200
Leavers : Oct 2021 - Sept 2022 is 2380

Nov 2021 - Oct 2022 is 2400

So my calculation for Oct 2021 - Sept 2022 would be:
2380 / (6500+5400)/2) * 100

Any help is very much appreciated

1 ACCEPTED SOLUTION  Community Support

Hi @anony711 ,

First we need a calendar table like: relationships: ``````Measure =
VAR _min_date = MIN('Calendar'[Date])
VAR _max_date = EDATE(_min_date,12)
VAR _start_monthyear = MAX('Calendar'[MonthYear])
VAR _end_monthyear = CALCULATE(MAX('Calendar'[MonthYear]),FILTER(ALL('Calendar'),'Calendar'[Date]=_max_date-1))
VAR _leavers = CALCULATE(SUM('Table3'[Number of leavers]),FILTER(ALL('Calendar'),'Calendar'[Date]>=_min_date&&'Calendar'[Date]<_max_date))
VAR _number_of_employees_at_the_start = CALCULATE(MAX('Table1'[Starting headcount]),'Calendar'[Date] = _min_date)
VAR _max_date_2 = CALCULATE(MAX('Table2'[Date]),FILTER(ALL('Table2'),'Table2'[Date]<_max_date))
VAR _number_of_employees_at_the_end = CALCULATE(MAX('Table2'[Ending headcount]),'Calendar'[Date] = _max_date_2)
VAR _result = DIVIDE(_leavers,(_number_of_employees_at_the_start+_number_of_employees_at_the_end)/2)
RETURN
_result``````

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data  Community Support

Hi @anony711 ,

First we need a calendar table like: relationships: ``````Measure =
VAR _min_date = MIN('Calendar'[Date])
VAR _max_date = EDATE(_min_date,12)
VAR _start_monthyear = MAX('Calendar'[MonthYear])
VAR _end_monthyear = CALCULATE(MAX('Calendar'[MonthYear]),FILTER(ALL('Calendar'),'Calendar'[Date]=_max_date-1))
VAR _leavers = CALCULATE(SUM('Table3'[Number of leavers]),FILTER(ALL('Calendar'),'Calendar'[Date]>=_min_date&&'Calendar'[Date]<_max_date))
VAR _number_of_employees_at_the_start = CALCULATE(MAX('Table1'[Starting headcount]),'Calendar'[Date] = _min_date)
VAR _max_date_2 = CALCULATE(MAX('Table2'[Date]),FILTER(ALL('Table2'),'Table2'[Date]<_max_date))
VAR _number_of_employees_at_the_end = CALCULATE(MAX('Table2'[Ending headcount]),'Calendar'[Date] = _max_date_2)
VAR _result = DIVIDE(_leavers,(_number_of_employees_at_the_start+_number_of_employees_at_the_end)/2)
RETURN
_result``````

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data Announcements #### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023. #### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers! #### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February. #### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders. Top Solution Authors
Top Kudoed Authors
Users online (3,074)