Forum Discussion
Events in progress with datediff
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:
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
5 Replies
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-5c8e4aca38cHi 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.
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
If the enddate is 1-1-2100Don'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.
