Net Work Days 06-23-2022 07:18 AM

[Greg_Deckler]

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

[deshbali]

I don't know what I am doing wrong, for sept it return 2 days :

NetWorkDaysnew =

    VAR __Date1 =  dt"2022-09-05"

    VAR __Date2 = dt"2022-09-06"

    VAR __Date1a = MINX( { __Date1, __Date2 },[Value])

    VAR __Date2a = MAXX( { __Date1, __Date2 },[Value])

    VAR __Calendar =

        ADDCOLUMNS(

            CALENDAR(__Date1a, __Date2a),

            "__WeekDay",

            WEEKDAY([Date],2)

        )

RETURN

    COUNTX(

        FILTER(

            __Calendar,

            [__WeekDay] < 6

        ),

        [Date]

    )

Same pattern for all odd months Jan, Mar, May and for some months like feb and Nov it returns null

[PetersNL]

@Greg_Deckler Sorry for dredging up this old thread, but I have the following question:

When I use the NetWorkDaysHolidaysDaysHoursMinutes formula, I can get values like: 

4 Days 19 Hours 28 Minutes

Though I'd like to measure the hours and minutes only when the office is open. Can you maybe send me in the right direction how someone would tackle this problem?

Thanks in advance!

[Emanuel]

@Greg_Deckler I am trying to add x number of working days to the create date in order to get the review date. Any ideas how can I do that?

The specific requirement is:

for priority 1 add 5 working days, for P2 add 10 working days for P3&4 add 20 working days.

Thank you

[Greg_Deckler]

@Emanuel Hmm, maybe something like:

Measure =
  VAR __AddDays = 
    SWITCH(MAX('Table'[Priority]),
      "P1",5,
      "P2",10,
      20
    )
  VAR __CreatedDate = MAX('Table'[created date])
  VAR __Calendar1 = CALENDAR(MAX(__CreatedDate+1,__CreatedDate+30)
  VAR __Calendar2 = ADDCOLUMNS(__Calendar1,"WeekDay",WEEKDAY([Date],2))
  VAR __Calendar3 = FILTER(__Calendar2,[WeekDay]<6),[Date])
  VAR __Set = TOPN(__AddDays,__Calendar3,[Date],1)
RETURN
  MAXX(__Set,[Date])

[katerinepr]

Hi @Greg_Deckler , opening this thread. I have this same scenario, however, I need to exclude weekends. May I ask for your inputs/help on this? Thank you.

[Greg_Deckler]

@katerinepr Well, let's see, maybe something like:

Column =
  VAR __Date = [Query date received]
  VAR __IncludeWeekends = [Requirement] //assumes true/false
  VAR __Days = [Days to add] // assumes a numeric value
  VAR __Calendar = ADDCOLUMNS(CALENDAR(__Date, __Date + __Days * 2),"Weekday",WEEKDAY([Date],2))
  VAR __CalendarX = 
    ADDCOLUMNS(
      FILTER(__Calender,[Weekday] < 6),
      "Days", VAR __MyDate = [Date] RETURN COUNTROWS(FILTER(__Calendar,[Date] <= __MyDate)
    )
RETURN
   IF(__IncludeWeekends, __Date + __Days, MAXX(FILTER(__CalendarX,[Days] = __Days),[Date]))

[katerinepr]

Can I add switch function to filter per country? I have 9 countries, and all of them have different # of days to add.

[juncco888]

@Greg_Deckler,

Thanks for these measures...  They have been bery useful.  I have a Holiday Table that has a date column for all of the holidays.  Is there a way to reference that column and have it add the dates to the Holidays1 variable in this  holiday measure:

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"

[BI_Analyticz]

Hi, This is really an amazing post. I need help in ignoring a list of dates in my holiday table. I can see that you have ignored 25th Dec for calculating the network days. But if I have a list of dates in my holiday table how to use that in your code. Please help.

And I just need the count in days only.

[Anonymous]

This is a great solution. 
just wondering if it is possible to pull this detail from a separate table where we can enter public holidays by year?

VAR Holidays1 = DATATABLE("Date",DATETIME,
    {
        {"12/25/2017 12:00:00 AM"}
    })

for e.g. a table will hold Public holidays like this:

Holidays date

01/01/2020

25/12/2020

01/01/2021

25/12/2021

26/12/2021

[BI_Analyticz]

@Anonymous @Greg_Deckler  Any luck on how to you use a list of holiday list please...

[Greg_Deckler]

@BI_Analyticz - I have an updated version of this in my book DAX Cookbook from Packt. If you just want the code, check out Chapter 7, Recipe 10 - https://github.com/gdeckler/DAXCookbook

[Greg_Deckler]

@Emanuel - Yeah, just get rid of your MAX and just use TODAY or NOW. I have an improved version of this in recipe 10 of chapter 2 of DAX Cookbook. https://github.com/gdeckler/DAXCookbook

[Jadnerb]

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.

[texmexdragon]

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

[Greg_Deckler]

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])

[Anonymous]

Hi @Greg_Deckler , Getting the attached error while using IF Condition. Kindly assist.

[Tan]

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

[Greg_Deckler]

@Tan - 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.

[kman42]

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?