cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jcatindoy Regular Visitor
Regular Visitor

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

Accepted Solutions
danextian New Contributor
New Contributor

Re: NETWORKDAYS with multiple country holidays

Hi@jcatindoy

 

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] )
)
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
9 REPLIES 9
danextian New Contributor
New Contributor

Re: NETWORKDAYS with multiple country holidays

Hi@jcatindoy

 

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] )
)
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
danextian New Contributor
New Contributor

Re: NETWORKDAYS with multiple country holidays

...and the PBIX

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

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
jcatindoy Regular Visitor
Regular Visitor

Re: NETWORKDAYS with multiple country holidays

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.

danextian New Contributor
New Contributor

Re: NETWORKDAYS with multiple country holidays

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

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
danextian New Contributor
New Contributor

Re: NETWORKDAYS with multiple country holidays

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

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
jcatindoy Regular Visitor
Regular Visitor

Re: NETWORKDAYS with multiple country holidays

Hi @danextian,

 

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

danextian New Contributor
New Contributor

Re: NETWORKDAYS with multiple country holidays

You're welcome.

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
dineshelpitiya Frequent Visitor
Frequent Visitor

Re: NETWORKDAYS with multiple country holidays

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

dineshelpitiya Frequent Visitor
Frequent Visitor

Re: NETWORKDAYS with multiple country holidays

Hi @danextian,

 

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

 

Thank and Regards,

Dinesh