cancel
Showing results for
Did you mean:
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.

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
New Contributor

## Re: NETWORKDAYS with multiple country holidays

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_```

• 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

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."
9 REPLIES 9
New Contributor

## Re: NETWORKDAYS with multiple country holidays

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_```

• 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

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."
New Contributor

## Re: NETWORKDAYS with multiple country holidays

...and the PBIX

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
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.

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

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."
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_

Frequent Visitor

Hi @danextian,