topic Re: Net Work Days in Quick Measures Gallery

Net Work Days

Greg_Deckler — Fri, 24 Aug 2018 12:47:29 GMT

Recreates the NETWORKDAYS function from Excel with some bonus measures and one that needs fixed or demonstrates a bug/documentation glitch.

NetWorkDays calculates the number of days between two dates sans weekends.

NetWorkDays = 
VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])

DaysHoursMinutes provides a nice display format for the number of days, hours and minutes between two dates

DaysHoursMinutes = DATEDIFF(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]),DAY) 
& " Days " & HOUR(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1)) 
& " Hours " & MINUTE(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1)) & " Minutes"

NetWorkDaysHoursMinutes displays net work days in a nice display format

NetWorkDaysHoursMinutes = 
VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date]) & " Days " & 
HOUR(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1)) & " Hours " 
& MINUTE(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1)) & " Minutes"

Finally, this one tries incorporates holidays. While the documentation indicates that you can use DATE, you cannot. But the following code does work to remove December 25th as a work day.

NetWorkDaysHolidays = 
VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]))
//VAR Holidays = DATATABLE("Date",DATETIME,{{}})
VAR Holidays1 = DATATABLE("Date",DATETIME,
    {
        {"12/25/2017 12:00:00 AM"}
    })
VAR Calendar2 = EXCEPT(Calendar1,Holidays1)
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar3,[WeekDay]<6),[Date])

Finally, finally, this one is Holidays with days, hours and mintues:

NetWorkDaysHolidaysDaysHoursMinutes = 
VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]))
//VAR Holidays = DATATABLE("Date",DATETIME,{{}})
VAR Holidays1 = DATATABLE("Date",DATETIME,
    {
        {"12/25/2017 12:00:00 AM"}
    })
VAR Calendar2 = EXCEPT(Calendar1,Holidays1)
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar3,[WeekDay]<6),[Date]) & " Days " & 
HOUR(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1)) & " Hours " 
& MINUTE(MOD(MAX(NetWorkDays[review date]) - MAX(NetWorkDays[created date]),1)) & " Minutes"

eyJrIjoiYjFhNWIwMmMtMTlhOC00YjNkLTlmNmMtMGYxMjcwOWQzZWRkIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Re: Net Work Days

BachFel — Thu, 19 Apr 2018 14:29:26 GMT

Thanks for your help,

NetWorkDays =
VAR Calendar1 = CALENDAR(MAX(KontElemente[Datum der Besichtigung]);MAX(today())

I didn´d understand the part.

VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])

Can u help me out?

Re: Net Work Days

Greg_Deckler — Thu, 19 Apr 2018 14:35:02 GMT

If you are referring to the VAR Calendar2 part, what that does is adds a column to the temp calendar table using the WEEKDAY function with the parameter 2, which essentially codes things M=1, T=2, W=3, Th=4, F=5, Sa=6, Su=7. So, the next step, the RETRUN filters out anything 6 or 7 to exclude Saturdays and Sundays.

Re: Net Work Days

BachFel — Thu, 19 Apr 2018 14:39:28 GMT

It´s not working. Guess I´m the problem and not the formula

Re: Net Work Days

Greg_Deckler — Thu, 19 Apr 2018 15:47:59 GMT

You are missing a ) at the end of your line beginning with VAR Calendar1

Re: Net Work Days

Anonymous — Tue, 02 Oct 2018 11:37:11 GMT

Very nice scripts, but what if I want to aggregate the network days by another column from the NetWorkDays table?

Eg. if I add a "Name" column to the NetWorkDays table and add John and Jane Doe and one more row:

When using the NetWorkDays measure it works on a row by row level, but if I try to aggregate by the 'Name' column the measure seems to be incorrect:

Any idea why and how to fix it?

I have included the modified PBIX file.

Re: Net Work Days

GonzaloS — Thu, 03 Jan 2019 19:36:14 GMT

What if I have some empty fields?

In my query the "review date" for ongoing item is blank. and this measure provide gives me an error.

I don't wan't to filther them out. I still want to show all row in my dashboard.

Thank you in advance.

Regards

Re: Net Work Days

Anonymous — Fri, 25 Jan 2019 02:09:34 GMT

Hi Greg, i am using a direct query and it throws up an error saying countx is not allowed as part of calculated columns dax expressions on Direct Query Mode.. Please Help!!!!

Re: Net Work Days

Greg_Deckler — Fri, 25 Jan 2019 11:33:49 GMT

Are you sure you are using the formula in a measure and not in a Column? According to this article, COUNTX should work in a measure:

https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/dax-formula-compatibility-in-directquery-mode-ssas-2016?view=sql-server-2017

Re: Net Work Days

Anonymous — Fri, 25 Jan 2019 12:59:05 GMT

Hi Greg,
I am definatly using a measure!
Below is the Error Message:
MdxScript(Model) (189, 17) Calculation error in measure 'fact_CRM_Incident'[Business Days Active]: The start date in Calendar function can not be later than the end date.

I would like to also reference my holiday flag column which is a Yes/No field in my dim_date table.

On another note the Closed on date column contains blanks if a ticket is in an "active" or Open state in which case the business days calc should continue to count till current day until a closed date is added in the dataset.

Appreciate the help mate.

Re: Net Work Days

Anonymous — Mon, 13 May 2019 15:16:51 GMT

Is there a way to calculate difference between Today() and a given date?

Re: Net Work Days

Greg_Deckler — Mon, 13 May 2019 19:11:10 GMT

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.

Re: Net Work Days

Anonymous — Tue, 14 May 2019 13:03:34 GMT

Thanks Greg_Deckler this works like a charm.

Re: Net Work Days

Anonymous — Tue, 16 Jul 2019 21:16:16 GMT

I am receiving the same error. Did you ever figure out the solution?

Thanks!

Re: Net Work Days

Anonymous — Thu, 25 Jul 2019 14:31:20 GMT

I infact did i used Greg's solution and created another measure to calculate weekends and holidays and subtracted from the above solution.

Anonymous wrote:

I am receiving the same error. Did you ever figure out the solution?

Thanks!

Re: Net Work Days

Anonymous — Tue, 29 Oct 2019 20:38:43 GMT

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?

Re: Net Work Days

Greg_Deckler — Wed, 30 Oct 2019 02:23:42 GMT

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

Re: Net Work Days

Jadnerb — Mon, 04 Nov 2019 16:50:09 GMT

Based on your "NetWorkDaysHoursMinutes"

I tried using MAX(TODAY()) but it didn't work, I used MIN and MAX. However, I am getting text (Days Hours Minutes) and cannot change the format to whole number.

Re: Net Work Days

Anonymous — Fri, 21 Feb 2020 21:43:47 GMT

Greg_Deckler I tried using your formula early on, but it would not allow me since I have some Ship Dates that start before Due Date. The error message said the formula could not allow for that.

Re: Net Work Days

Greg_Deckler — Sat, 22 Feb 2020 17:15:40 GMT

Oh, that would be the CALENDAR function. You would just have to create an IF statement to check which date is larger or smaller and then create your CALENDAR appropriately. Like this:

NetWorkDays = VAR __CreatedDate = MAX(NetWorkDays[created date]) VAR __ReviewDate= MAX(NetworkDays[review date]) VAR Calendar1 = IF( __CreatedDate < __ReviewDate, CALENDAR(__CreatedDate,__ReviewDate), CALENDAR(__ReviewDate,__CreatedDate), ) VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2)) RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])