cancel
Showing results for
Did you mean:
Highlighted
Helper I

## 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
Highlighted
Super User I

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

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
12 REPLIES 12
Highlighted
Super User I

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

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Highlighted
Super User I

## Re: NETWORKDAYS with multiple country holidays

...and the PBIX

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Highlighted
Helper I

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

Highlighted
Super User I

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

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Highlighted
Super User I

## Re: NETWORKDAYS with multiple country holidays

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

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Highlighted
Helper I

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

Highlighted
Super User I

## Re: NETWORKDAYS with multiple country holidays

You're welcome.

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Highlighted
New Member

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

Highlighted
New Member

## Re: NETWORKDAYS with multiple country holidays

Hi @danextian,

Thank and Regards,

Dinesh

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors