Forum Discussion
Resolver IIMonthly Historical Headcount
Hi Everyone
I have a date table that is joined to an employee table via the hire date. What i am trying to find out is how many active employees i have for a given month based on a date slicer. How can i do that?
Here some sample date and output
| ID Number | Name | Hire Date | Termination Date | Status |
| 1 | Iron Man | 2/1/2018 | 12/31/9999 | ACTIVE |
| 2 | Capt America | 3/1/2018 | 12/31/9999 | ACTIVE |
| 3 | Dr. Strange | 4/1/2018 | 12/31/9999 | ACTIVE |
| 4 | Black Widow | 5/1/2018 | 12/31/9999 | ACTIVE |
| 5 | Scarlett Witch | 6/1/2018 | 11/1/2018 | TERM |
| 6 | Hawkeye | 7/1/2018 | 12/31/9999 | ACTIVE |
| 7 | Thor | 8/1/2018 | 10/1/2018 | TERM |
| 8 | Spiderman | 9/1/2018 | 12/31/9999 | ACTIVE |
| 9 | Ant Man | 10/1/2018 | 12/31/9999 | ACTIVE |
OUTPUT
| MONTH | HEADCOUNT |
| 2/1/2018 | 1 |
| 3/1/2018 | 2 |
| 4/1/2018 | 3 |
| 5/1/2018 | 4 |
| 6/1/2018 | 5 |
| 7/1/2018 | 6 |
| 8/1/2018 | 7 |
| 9/1/2018 | 8 |
| 10/1/2018 | 8 |
| 11/1/2018 | 7 |
| 12/1/2018 | 7 |
2 Replies
- OwenAuger
Super User
Hi Jorgast
What you're wanting is an "events in progress" measure.
In your case, I would probably leave your data structure unchanged and use a measure like this:
Headcount at end of period = VAR MaxDate = MAX ( 'Date'[Date] ) VAR TermDateFilter = DATESBETWEEN ( Employee[Termination Date], MaxDate, BLANK () ) // Termination Date >= MaxDate VAR HireDateFilter = DATESBETWEEN ( Employee[Hire Date], BLANK (), MaxDate ) // Hire Date <= MaxDate RETURN CALCULATE ( COUNTROWS ( Employee ), TermDateFilter, HireDateFilter )This measure gives you the Headcount as at the last day filtered (e.g. the last day of the month if filtered on month).
Here is a sample model using this measure using the data you posted:
If you search for events in progress, there are various approaches out there including:
- Examples in the context of DAX queries:
https://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf - Events in progress measures:
https://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/ - Restructuring data so that there is one row per date for each event
https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/
Regards,
Owen
JorgastI think i am missing something. I plugged in the measure, and renamed the Hire/Term fields to meet my needs. When i run the calculation it shows me the number of agents that were hired by the end of the month. I need to know all the active agents in a given month and year.
- Examples in the context of DAX queries:
