Forum Discussion
Count Employees over time from start and term date
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 |
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.
6 Replies
- Greg_Deckler
Community Champion
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-hoAdvocate I
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).
- 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.
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.
