Count Employees over time from start and term date

[laronny]

I'm wondering how to count monthly employees from their respective start and term dates using dax? 

ID	Name	start date	Term date	Jan 2024	Feb 2024
001	John Doe	12/1/2023	1/28/2024	.903	0
002	Mary Jane	1/1/2024	3/1/2024	1	1
003	Jane Doe	11/5/2023	12/31/9999	1	1
Total				2.903	2

[v-jianpeng-msft · Accepted solution]

Hi, @laronny 

Based on the sample data you provided, I've created the following dataset:

First, I created a date table:

CalenderTable = ADDCOLUMNS(CALENDAR(DATE(2024,1,1),DATE(2024,12,31)),
    "MonthID",MONTH([Date]),
    "Year",YEAR([Date])
)

I've created the following two measures:

2024 January = 
VAR _date = MAXX(FILTER('CalenderTable','CalenderTable'[MonthID] = 1),'CalenderTable'[Date])
VAR _mindate = MINX(FILTER(ALL('CalenderTable'),'CalenderTable'[MonthID] = 1),'CalenderTable'[Date])
VAR _diff = DATEDIFF(_date,SELECTEDVALUE('Table'[Term date]),DAY)
VAR _diff2 = DATEDIFF(_date,_mindate,DAY)
RETURN 
IF(_diff < 0,IF(_diff>_diff2,(DATEDIFF(_mindate,SELECTEDVALUE('Table'[Term date]),DAY)+1)/(DATEDIFF(_mindate,_date,DAY)+1),0),1)

2024 February = 
VAR _date = MAXX(FILTER('CalenderTable','CalenderTable'[MonthID] = 2),'CalenderTable'[Date])
VAR _mindate = MINX(FILTER('CalenderTable','CalenderTable'[MonthID] = 2),'CalenderTable'[Date])
VAR _diff = DATEDIFF(_date,SELECTEDVALUE('Table'[Term date]),DAY)
VAR _diff2 = DATEDIFF(_date,_mindate,DAY)
RETURN IF(_diff < 0,IF(_diff>_diff2,(DATEDIFF(_mindate,SELECTEDVALUE('Table'[Term date]),DAY)+1)/(DATEDIFF(_mindate,_date,DAY)+1),0),1)

Put it into a table and find that the total is incorrect:

This is very normal behavior. When our measure calculation logic is complicated, we usually get an incorrect total. So I created the following two correct measures:

2024 January Correct = SUMX(VALUES('Table'[Term date]),[2024 January])

2024 February Correct = SUMX(VALUES('Table'[Term date]),[2024 February])

Make these measures into field parameters so that they can be selected by the slicer:

The results are as follows:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

hi @laronny ,

check these:

https://community.fabric.microsoft.com/t5/Desktop/Count-total-active-employees-in-each-month/m-p/807...

https://community.fabric.microsoft.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termi...

[Houston-ho]

i believe you need to calculate the number of days between 2 dates using DATEDIFF(<Date1>, <Date2>, DAY), also EOMONTH(<start_date>, 0) to get the last date of the month. Therefore you could get 28/31 = 0.903 for John in Jan 2024.

[laronny]

This is great, but I have dummy dates for active instead of nulls. @Greg_Deckler , do you know how I can either change dummy date to null/blank or have the dax work with 12/31/9999?

[Houston-ho]

probably you need to compare system today date to calculate the number of days up to today (or you may have a selector slicer on screen as your "as of date") to calculate dynamically.

If you have a slicer (single selction) from calendar table, you could use the dax SELECTEDVALUE to obtain that, then using another DAX DATEDIFF to calculate the number of days between start date and the as of date (or system today).

[Greg_Deckler]

@laronny Open Tickets - Microsoft Fabric Community