Net Work Days
Is there a way to calculate difference between Today() and a given date?
Anonymous - Sure, you could do something like this:
NetWorkDays = VAR Calendar1 = CALENDAR(TODAY(),[Any Given Date]) VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2)) RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])
If necessary, reverse the parameters for the CALENDAR function.
Thanks Greg_Deckler this works like a charm.
Sorry to re-open an old thread, but I'm not getting the right answer for the number of days using the COUNTX function in the NetWorkDaysHoursMinutes measure. If I have a start date of 10/11/2019 and an end date of 10/12/2019, depending on the start and end times, it could be 0 hours or 1 hour. E.g.
10/11/2019 11:00 to 10/12/2019 9:00 should return "0 Days 22 hours 0 minutes", but it returns "1 Day 22 hours 0 minutes"
Is there a way to fix this?
Anonymous, Ahhhh, the boundary cases. Glad you did re-open the thread. Try this variation out, should account for the boundary case that you specify and frankly cleans up the code a bit.
NetWorkDaysHoursMinutes = VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date])) VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2)) VAR Days = COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date]) VAR Hours = HOUR(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1)) VAR Minutes = MINUTE(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1)) VAR NetWorkDaysHoursMinutes = SWITCH(TRUE(), Days = 1 && HOUR(MAX(NetWorkDays[review date])) < HOUR(MAX(NetWorkDays[created date])), "0 Days " & Hours & " Hours " & Minutes & " Minutes", Days & " Days " & Hours & " Hours " & Minutes & " Minutes" ) RETURN NetWorkDaysHoursMinutes
