Net Work Days

Super User
4997 Views
Highlighted
Super User
Posts: 10,774
Registered: ‎07-11-2015

Net Work Days

[ Edited ]

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"

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Attachment
Regular Visitor
Posts: 38
Registered: ‎12-04-2017

Re: Net Work Days

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?

Super User
Posts: 10,774
Registered: ‎07-11-2015

Re: Net Work Days

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Regular Visitor
Posts: 38
Registered: ‎12-04-2017

Re: Net Work Days

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

Super User
Posts: 10,774
Registered: ‎07-11-2015

Re: Net Work Days

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

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Regular Visitor
Posts: 41
Registered: ‎09-21-2017

Re: Net Work Days

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:

Table.PNG

 

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:

Aggregation.PNG

Any idea why and how to fix it?

I have included the modified PBIX file.

Attachment
New Member
Posts: 1
Registered: ‎01-03-2019

Re: Net Work Days

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

Frequent Visitor
Posts: 12
Registered: ‎09-12-2018

Re: Net Work Days

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!!!!

Super User
Posts: 10,774
Registered: ‎07-11-2015

Re: Net Work Days

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-d...

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Frequent Visitor
Posts: 12
Registered: ‎09-12-2018

Re: Net Work Days

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.