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 or follow my page on
Facebook @DAXJutsuPBI.
