Historic Headcount numbers with a SCD2 HR Data Model - Sample Data Attached

[ayran]

Hi All

Longtime user of Tableau but using PowerBI currently. I need some help. I have a HR Data Model in which each dimension is a type 2 with effectiveStart and end dates for each record. The fact table also contains history in regards to hiredate and employment end date 

I have a requirement where I need to do historic trend graphs of head count as well as other metrics. For example what was the headcount every month for the last 2 years? This needs to be sliceable by my 3 dimensions attached (Employee Details, Job Name and Department). Therefore I need to constantly ensure that the current slice of data in my graph satisfies the effectivestart and end dates of each dimension. I'm not sure how to achieve this in PowerBI? I know in my fact table I can do a sliding window on the employment start and end dates in order to give me what I need, however as soon as I want to slice by Department for instance, I would need to ensure I am only selecting the active department name at a given point in time.

The two main requirements I have are Headcount (count distinct [uniqueid]) and Sum of FTE counts.

For example if I have 10 employees with [FTE Value] of 1, the FTE Count is 10.

If I have 10 employees with [FTE Value] of 0.5, the FTE Count is 5.

In the instance of FTE Counts, its obviously more difficult than doing a count distinct since its a SUM operation, so it's imperative I only ever count ONE active value in each aggregation.

Is this best done in DAX? My real dataset has around 15m rows in the fact table and about 10-20k in the dimensions (of which there are plenty more)

Please see sample dataset and PBIX file attached. Any help would be appreciated and I hope i've been clear in what I need.

Sample Data:
https://docs.google.com/spreadsheets/d/19racKsDBQYOYOCRVyyRhQtop65EURW2o/edit?usp=share_link&ouid=11...

Sample PBIX File:

https://drive.google.com/file/d/10pkIQy5NWc8oQOh6uWLvldz64LAcL7Mn/view?usp=share_link

[Anonymous · Accepted solution]

Hi @ayran,

In fact, these requirements can be achieved in Dax.  You need to create and extract different range of records and saving these ID lists to different variables.  

After these steps, you can use EXCEPT, INTERSECT functions to compare these lists. (create two variables with lists based filter on previous date range and current date range)

Logic :
EXCEPT fucntion with 'current' and 'previous': new employees.
INTERSECT fucntion with 'current' and 'previous' : old employees.
EXCEPT fucntion with 'previous' and 'current': left employees.

Notice:

These operations required to loading large number of records and compare with them. So they will spend large amount or memory resource and case the performance issue when you try to execute this formula on a long date range.

EXCEPT function (DAX) - DAX | Microsoft Learn

INTERSECT function (DAX) - DAX | Microsoft Learn

Regards,

Xiaoxin Sheng