Power BI / Power Pivot DAX Challenge
Compute number of active staff sliced by various parameters
Supposing you get the following HR list showing employees and when they joined a certain company. The “Date Terminated” column shows when an employee vacated the company, where the blank rows imply they’re still working for the company.
The Challenge
A HR professional would wish to create summaries such as Active Staff Count in a given year (like the one shown below), a split of staff that are active and terminated by department in a given year, and so forth.
What DAX measures would you use?
You can attempt using Excel Power Pivot or/and Power BI Desktop
Post your comments below
…and…
Let us educate one another
Discussion
2 Comments
Leave A Comment
Related
If you found some value from this, please explore our other articles below!
![Using Excel Power Query to Unpivot Data [Extremely Easy That It Will Shock You]](https://datacycleanalytics.com/wp-content/uploads/2018/09/YouTube-Thumbnail-Using-PQ-to-Unpivot-Data-500x383.png?x66433)
I would first create a Date table with an active relationship with data[Date of Hire] and an inactive relationship with data[Date Terminated].
Then the measures could look like below:
(sample pbix here https://ozerconsulting-my.sharepoint.com/:u:/g/personal/owenauger_ozerconsulting_onmicrosoft_com/EQ3gvilLbcNKiUoN3-jNKN8BuT8Ud7kOcN-Yf6DNpD6dZA?e=gC4mpT )
Hired =
COUNTROWS ( data )
Fired =
CALCULATE (
COUNTROWS ( data ),
USERELATIONSHIP ( data[Date Terminated], ‘Date'[Date] ),
NOT ISBLANK ( data[Date Terminated] )
)
Active Staff =
— Returns count of staff who were active as at the last date selected
VAR MaxDate =
MAX ( ‘Date'[Date] )
RETURN
CALCULATE (
COUNTROWS ( data ),
ALL ( ‘Date’ ),
data[DateOfHire] <= MaxDate, OR ( ISBLANK ( data[Date Terminated] ), data[Date Terminated] > MaxDate )
)
There are some different ways to write the last measure (an ‘events in progress’ type measure) but I have found the above code to perform reasonably well in models I have worked with.
Thank you Owen for this elegant solution and providing the attached file. That “Active Staff” measure gave me some headache. I had the Fired & Hired working but this one I thought I would initially need to have a measure for “Hired Running Total” and “Fired Running Total” and the subtract.
Am applying this