Count Employees over time from start and term date

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.