Events in progress with datediff

[RobBeijers312]

Hi,

For a report on sick days I need to calculate the datediff between startdate and enddate. If the enddate is 1-1-2100 the enddate is today. While this is no problem I cant get it to work if I need to use it per period. For example, employeeid 8 has 162 sick days till today. 86 of them are in 2024 and 76 are in 2025. Can anyone help me with this measure?

This is the current measure:

Aantal dagen ziek =

VAR mindatum = IF(MIN(Dimdatum[Date]) <= MAX(Ziekmeldingen[startdate]),MAX(Ziekmeldingen[startdate]),MIN(Dimdatum[Date]))

VAR maxdatum = IF(MAX(Ziekmeldingen[enddate]) <= MAX(Dimdatum[Date]),MAX(Ziekmeldingen[enddate]),MAX(Dimdatum[Date]))

VAR aantaldagenziek = CALCULATE(DATEDIFF(mindatum,maxdatum,DAY),REMOVEFILTERS(Dimdatum),Ziekmeldingen[startdate] <= maxdatum,Ziekmeldingen[enddate] >= mindatum)

[lbendlin]

 If the enddate is 1-1-2100

Don't do that.  Leave the end date BLANK in these cases. Otherwise your calendar is ballooning unnecessarily.

Consider using CALENDAR and COUNTROWS(INTERSECT()) as an alternative approach.

[amitchandak]

@RobBeijers312 ,

You can use datediff or workdays as per need

New column =

var _max = If([enddate] = date(2100,1,1), today(), [enddate])

return

datediff([startdate], _max, day)

or networkdays([startdate], _max, 1)

Power BI - Business day with and without using DAX Function NETWORKDAYS: https://www.youtube.com/watch?v=Qs03ZZXXE_c
https://medium.com/@amitchandak/power-bi-dax-function-networkdays-5c8e4aca38c 

[RobBeijers312]

Hi @amitchandak ,

Thanks for your response.

I probably wasn't really clear in my questioning, but the new column ain't the problem. For a report I need the sick days per month and per year. For example, employeeid 8 has 162 sick days till today. 86 of them are in 2024 and 76 are in 2025.

[amitchandak · Accepted solution]

@RobBeijers312 , refer

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

Power BI- DAX: Get all dates between the Start and End date: https://youtu.be/y7AL_quvq5w

[RobBeijers312]

Hi @amitchandak 

Many thanks for the instruction vids. This was exactly what I needed!