Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

NETWORKDAYS with multiple country holidays

Hi, I am computing the difference between two working dates to know the lenght of days used, the formula is below;

 

TAT = CALCULATE(SUM(CalendarTable[WorkingDay]),
DATESBETWEEN(CalendarTable[Date],
'Processed Data'[Date of failure],
'Processed Data'[Date of repair])
)

 

WorkingDay = SWITCH(CalendarTable[WeekNo],1,0,7,0,1)

 

But i realized that i forgot to add the holidays in the equation of the working day, on top of that, i have difficulty because my data is across different countries that does not share the same holidays.

 

I shared the data below.

 

https://drive.google.com/open?id=1RmBqHxvPOYZ-wTEplvaOlln_ajjnnKP8

 

Thanks in advance for the help.

 

I have a column for the country in each row so it can be used to filter the TAT computation based on the country holiday list, I guess.

 

I have been at it for hours and can't seem to figure out how to do it. Any help will be appreciated.

 

 

1 ACCEPTED SOLUTION
danextian
Super User
Super User

Hi@Anonymous

 

For this use case, you need to calculate first the number of weekdays within your specified date range then deduct the number of weekdays that are holidays. Here's what I've come up with:

 

NETWORKDAYS W/ HOLIDAYS = 
VAR HOLIDAYS_ =
    CALCULATE (
        SUM ( Holidays[Is Weekday?] ),
        DATESBETWEEN (
            Holidays[Date],
            'Processed Data'[Date of failure],
            'Processed Data'[Date of repair]
        ),
        Holidays[Country] = EARLIER ( 'Processed Data'[Country] )
    )
VAR REGuLAR_NETWORKKDAYS_ =
    CALCULATE (
        SUM ( Dates[Is Weekday?] ),
        DATESBETWEEN (
            Dates[Date],
            'Processed Data'[Date of failure],
            'Processed Data'[Date of repair]
        )
    )
RETURN
    REGULAR_NETWORKKDAYS_ - HOLIDAYS_

Please note that:

  • HOLIDAYS and Dates tables are disconnected tables. They don't have any relationships with your fact table.
  • DATESBETWEEN() behaves similarly with NETWORKDAYS in Excel. It aggregates the values within a specified date range and not the difference between the end and start dates. That being said, you might want to deduct 1 from the measure above and return 0 if after deducting 1 the result is negative.

 

Also, you can create a date calendar in DAX or M without pulling in data from an externa source. Here are sample codes:

 

M - 

let
    Source = List.Dates(#date(2018, 1, 1), Number.From(DateTime.Date(DateTime.LocalNow()) - #date(2018,1,1)) +1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
    #"Inserted Day Name" = Table.AddColumn(#"Changed Type", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Added Custom" = Table.AddColumn(#"Inserted Day Name", "Is Weekday?", each if [Day Name]="Saturday" or [Day Name]="Sunday" then 0 else 1, Int64.Type)
in
    #"Added Custom"

 

DAX

CALENDAR Table =
CALENDAR (
    MIN ( 'Processed Data'[Date of failure] ),
    MAX ( 'Processed Data'[Date of repair] )
)





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

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

14 REPLIES 14
danextian
Super User
Super User

Hi@Anonymous

 

For this use case, you need to calculate first the number of weekdays within your specified date range then deduct the number of weekdays that are holidays. Here's what I've come up with:

 

NETWORKDAYS W/ HOLIDAYS = 
VAR HOLIDAYS_ =
    CALCULATE (
        SUM ( Holidays[Is Weekday?] ),
        DATESBETWEEN (
            Holidays[Date],
            'Processed Data'[Date of failure],
            'Processed Data'[Date of repair]
        ),
        Holidays[Country] = EARLIER ( 'Processed Data'[Country] )
    )
VAR REGuLAR_NETWORKKDAYS_ =
    CALCULATE (
        SUM ( Dates[Is Weekday?] ),
        DATESBETWEEN (
            Dates[Date],
            'Processed Data'[Date of failure],
            'Processed Data'[Date of repair]
        )
    )
RETURN
    REGULAR_NETWORKKDAYS_ - HOLIDAYS_

Please note that:

  • HOLIDAYS and Dates tables are disconnected tables. They don't have any relationships with your fact table.
  • DATESBETWEEN() behaves similarly with NETWORKDAYS in Excel. It aggregates the values within a specified date range and not the difference between the end and start dates. That being said, you might want to deduct 1 from the measure above and return 0 if after deducting 1 the result is negative.

 

Also, you can create a date calendar in DAX or M without pulling in data from an externa source. Here are sample codes:

 

M - 

let
    Source = List.Dates(#date(2018, 1, 1), Number.From(DateTime.Date(DateTime.LocalNow()) - #date(2018,1,1)) +1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
    #"Inserted Day Name" = Table.AddColumn(#"Changed Type", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Added Custom" = Table.AddColumn(#"Inserted Day Name", "Is Weekday?", each if [Day Name]="Saturday" or [Day Name]="Sunday" then 0 else 1, Int64.Type)
in
    #"Added Custom"

 

DAX

CALENDAR Table =
CALENDAR (
    MIN ( 'Processed Data'[Date of failure] ),
    MAX ( 'Processed Data'[Date of repair] )
)





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

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable

Dear @danextian   ,

Plese help me to get out of this !!!

I want Values Row by row in Calculated column followed same way as you mentioned below and it is not giving me negtive values,Please find the Below Data whichi have and Screen Captures.

Thanks In Advance.

 

Dax Formula What I used:

TBL_Date=DATESBETWEEN('Calender Table'[Date].[Date],MasterData[Invoice Date],MasterData[Payment Date])
var TBL_FinalDate=
ADDCOLUMNS(TBL_Date,"Working day",IF(WEEKDAY([Date],2)>=6,0,1),
"Holiday",IFERROR(LOOKUPVALUE('Holiday-2019'[Holidaycount],'Holiday-2019'[Holidays - 2019,2020],[Date]),0)
)
return
SUMX(TBL_FinalDate,if([Working day]=1&&[Holiday]=0,1,0))Capture1.JPGCapture2.JPG

Please post a sample data that is not screen caps and a sample table of your expected result. 






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

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable

Invoice DatePosted DatePayment DateDate DueType of supplierInvoice Received on
13-06-201931-07-201906-12-201913-07-20193rd Party12-07-2019
15-04-201912-08-201906-12-201915-05-20193rd Party08-08-2019
22-10-201930-10-201913-12-201921-11-20193rd Party29-10-2019
24-10-201930-10-201913-12-201923-11-20193rd Party29-10-2019
31-10-201907-11-201916-12-201930-11-20193rd Party31-10-2019
29-10-201912-11-201913-12-201928-11-20193rd Party06-11-2019
31-10-201928-11-201906-12-201930-11-20193rd Party08-11-2019
04-11-201912-11-201904-12-201904-12-20193rd Party11-11-2019
05-11-201915-11-201906-12-201905-12-20193rd Party11-11-2019
31-10-201928-11-201906-12-201930-11-20193rd Party12-11-2019
31-10-201918-11-201913-12-201930-11-20193rd Party12-11-2019
31-10-201918-11-201913-12-201930-11-20193rd Party12-11-2019
01-11-201918-11-201930-12-201901-12-20193rd Party12-11-2019
31-10-201918-11-201913-12-201930-11-20193rd Party13-11-2019
08-11-201918-11-201930-12-201908-12-20193rd Party14-11-2019
28-05-201928-11-201906-12-201927-06-20193rd Party15-11-2019
12-11-201918-11-201930-12-201912-12-20193rd Party15-11-2019
31-10-201920-11-201913-12-201930-11-20193rd Party18-11-2019
01-10-201920-11-201913-12-201931-10-20193rd Party19-11-2019
04-10-201920-11-201913-12-201903-11-20193rd Party19-11-2019
05-11-201927-11-201906-12-201905-12-20193rd Party21-11-2019
06-11-201927-11-201906-12-201906-12-20193rd Party21-11-2019
09-11-201927-11-201906-12-201909-12-20193rd Party21-11-2019
14-11-201927-11-201906-12-201914-12-20193rd Party21-11-2019
15-10-201927-11-201912-12-201914-11-20193rd Party21-11-2019
01-12-201901-12-201913-12-201931-12-20193rd Party22-11-2019
01-12-201901-12-201913-12-201931-12-20193rd Party22-11-2019

 

The  Expected result in Excel:

Invoice Date to Receipt Date
(Calender Days )
Invoice Date to Payment Date
(Calender Days )
Receipt Date to Posting Date  
( Working Days )
Receipt Date to Payment Date
(Working Days)
Posting to Payment Date
-3-313062-31305                       -31,307
5-313002-31305                       -31,307
20-312842-31305                       -31,307
3-312984-31301                       -31,305
3-312981-31301                       -31,302
1-312982-31299                       -31,301
10-312892-31299                       -31,301
-9-312998-31292                       -31,300
13-312841-31298                       -31,299
2-312933-31295                       -31,298
2-312933-31295                       -31,298
4-312913-31295                       -31,298
2-312892-31292                       -31,294
2-312853-31287                       -31,290
42426520                           -247
0111                               -  
181911                               -  
12512611                               -  
16816911                               -  
81022                               -  
121422                               -  
575922                               -  
0333                               -  
3633                               -  
4733                               -  
101333                               -  
151823                                 1
182123                                 1
5813                                 2
101424                                 2
2927                                 5
214612                                 6
1221381016                                 6
8271319                                 6
6271521                                 6
0929                                 7
2232310                                 7
Anonymous
Not applicable

Thanks @danextian. I tried it but i am having error on the earlier statement, my column is not showing on the formula below,

 

NETWORKDAYS W/ HOLIDAYS =
VAR HOLIDAYS_ =
CALCULATE (
SUM ( Holiday[IsWorkingDay] ),
DATESBETWEEN (
Holiday[Date],
'Processed Data'[Date of failure],
'Processed Data'[Date of repair]
),
Holiday[Country] = EARLIER ( 'Processed Data'[Country] )
)
VAR REGuLAR_NETWORKKDAYS_ =
CALCULATE (
SUM ( CalendarTable[WorkingDay] ),
DATESBETWEEN (
CalendarTable[Date],
'Processed Data'[Date of failure],
'Processed Data'[Date of repair]
)
)
RETURN
REGULAR_NETWORKKDAYS_ - HOLIDAYS_

 

and I am getting this error, upon enter 

 

A single value for column 'Date of failure' in table 'Processed Data' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

By the way, this is supposed to be a calculated column and not a measure. 






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

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable

Hi @danextian,

 

Yes, i was adding it as a measure, that is the problem, the solution works flawlessly.  Thanks a lot for the help.

You're welcome.






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

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Hi danex, 

I have the exact same problem as jcatindoy. I tried out your method, it didn't workout. An error occured: A date column containing duplicate dates was specified in the call to function 'DATESBETWEEN'. This is not supported.

Here is my code, could you please take a look at it? Thanks!!

businessday = 

var _END =Fact[completedon]

var _START =Fact[createdon]

var _holiday =

CALCULATE (

SUM (Holidays[Isweekday]),

DATESBETWEEN (Holidays[holidaydate],_START,_END),

Holidays[countrycode]=EARLIER(Fact[countrycode])

)

var _workinghours =

SUMX(

CALCULATETABLE(

Dates,

DATESBETWEEN(Dates[Date],_START,_END),

Dates[IsWorkingDay] = TRUE()),

MAX(MIN(Dates[End],_END)-MAX(Dates[Start],_START),0)

)

return IF(_workinghours-_holiday>0,_workinghours-_holiday,BLANK())

Hi @Gabiiiii,

 

The first argument in DATESBWEEN requires a Date column that doesn't contain duplicates. It is possible that either your Dates[Date] or Holidays[holidaydate] has the duplicates but the culprit is most likely the latter. I would use something like this instead:

VAR __HOLIDAYS =
    CALCULATE (
        SUM ( Holidays[Weekday Holiday Count] ),
        FILTER (
            Holidays,
            Holidays[Holiday] >= EARLIER ( Data[Start] )
                && Holidays[Holiday] <= EARLIER ( Data[End] )
                && Holidays[Country] = EARLIER ( Data[Country] )
        )
    )

Please refer to this sample pbix - https://drive.google.com/file/d/1pbYAD2KZf-RFq2kf3vvmI0IcxHvwmoIg/view?usp=sharing 






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

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Hi @danextian,

 

Thank you very much for sharing this wonderful solution.

 

for same requiremnt im using this and the only diffrence is Holiday is maintaining at "ATC" level instead of "Countries" where ATC is unique same Country. and ATC coulmn in EMPLOYEE table Nither Calculated column Nor Measure, it is a regular column appear in Model.

 

while creating the measure got the similar issues mentioned by @Anonymous as below :

 

"Error: Calculation error in measure 'EMPLOYEE'[NETWORKDAYS W/ HOLIDAYS]: A single value for column 'FROM_DATE' in table 'EMPLOYEE' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

 

Code written was as below :

 

NETWORKDAYS W/ HOLIDAYS =:
VAR HOLIDAYS_ =
CALCULATE (
SUM ( HOLIDAY[ISWORKINGDAY] ),
DATESBETWEEN (
HOLIDAY[HOLIDAY],
EMPLOYEE[FROM_DATE],
EMPLOYEE[TO_DATE]
),
HOLIDAY[ATC] = EARLIER ( EMPLOYEE[ATC] )
)
VAR REGuLAR_NETWORKKDAYS_ =
CALCULATE (
SUM ( CALANDER(ISWORKINGDAY) ),
DATESBETWEEN (
CALANDER[Date],
EMPLOYEE[FROM_DATE],
EMPLOYEE[TO_DATE]
)
)
RETURN
REGULAR_NETWORKKDAYS_ - HOLIDAYS_

 

could you please help me to undestand what went wrong.

Hi @danextian,

 

after downloading your .pbix file i realised the mistake had done, and Now code is working as expectd.

 

Thank and Regards,

Dinesh

Mmm. That's weird. I just copied the formula from my working PBIX. Have you checked on the PBIX by the way?






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

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

...and the PBIX

 https://drive.google.com/open?id=126_RK2j9R-Gu_n7Nwh2SWouNe-pOTqdC






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

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.