POWER PIVOT (EXCEL) - Calculate headcount based start end date range

0
votes

I have in a Excel file 2 tab with employee`s data:

  1. Current = Personal data ( enrollment, name, gender, DOF, age, ....)
  2. Start/End = Contract information (enrollment, Start date, End date, leaving reason....)

In Power Pivot I created :

  • A data model with the 2 Tabs (current / StarEnd)
  • Calendar tab (from 01/01/2015 up to now)
  • Relationship between the 2 tab (current and StarEnd) using the Enrollment that are similar in both tab (Étiquettes de lignes).

Now I`d like to calculate the headcounts (Nb of employees actives) splited by month (axis categories) from the 2015 up to now (axis Series) in a Pivot Graphic, which can be able to use some filters like Gender, Classification and etc...

Graph_example_Id_like_to_build

See the Excel file that I'm working on EXCEL FILE_POWER PIVOT

1
excelpowerbipowerpivot
Please format your question to be more clear and understandable. – Griva
How Can I count the Nb of employees actives between a date range formed by Start and End date, taking in mind that if the end date is empty means that the employee still active. – Richard Coutinho

1 Answers

0
votes

The typical components to solve this are:

  1. Date table (without any relationship to the other tables)
  2. Measure using CALCULATE ... FILTER ... [startdate] <= Dates[Date] && [enddate] >= Dates[Date]

It's explained in detail in the first section of this blog post:

https://www.kasperonbi.com/get-values-within-a-start-and-end-date-using-powerpivot-dax/

Note since that post was written, new DAX functions were released that can help with creating the Date table e.g. CALENDARAUTO.