cancel
Showing results for
Did you mean:

# Re: Net Work Days

Super User
5541 Views
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!

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?

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!

Regular Visitor

## Re: Net Work Days

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

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

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:

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.

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

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

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

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