cancel
Showing results for 
Search instead for 
Did you mean: 

Re: Net Work Days

Super User
5603 Views
Super User
Super User

Net Work Days

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!


BachFel Regular Visitor
Regular Visitor

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?

Highlighted
Super User
Super User

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!


BachFel Regular Visitor
Regular Visitor

Re: Net Work Days

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

Super User
Super User

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!


Mholsen Regular Visitor
Regular Visitor

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.

GonzaloS New Member
New Member

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

christoR1 Frequent Visitor
Frequent Visitor

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
Super User

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!


christoR1 Frequent Visitor
Frequent Visitor

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.