Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Sir/Mdm,
I need some help with the Power BI query of adding a SLA of a day to a date while excluding Weekends and Public holidays
Have been trying to find similar answers here but has been futile.
Example: 30 Dec 2022 (friday) + 1 day SLA + excluding New Year Holiday; to return 4 Jan (since that 2 Jan is the New Year Observed Holiday + 1 day SLA)
In excel i could easily use workday to exclude the weekends and holidays + a day of SLA
Any advice and help with the code is truly appreciated. Thank you
Solved! Go to Solution.
Hi,
I am not sure if I understood your question correctly, but please check the below formula and the attached pbix file.
It is for creating a new column.
Expected result CC =
VAR _calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2023, 12, 31 ) ),
"@Dayname", FORMAT ( [Date], "dddd" )
)
VAR _holidaycalendar =
SUMMARIZE (
FILTER (
ADDCOLUMNS ( _calendar, "@holiday", [Date] IN DISTINCT ( Holiday[Holiday] ) ),
[@holiday] = TRUE ()
),
[Date]
)
VAR _actualstartdate =
ADDCOLUMNS (
Data,
"@actualstartdate",
MINX (
FILTER (
_calendar,
[Date] >= EARLIER ( Data[Delivery date] )
&& NOT ( [Date] IN _holidaycalendar )
&& [@Dayname] <> "Saturday"
&& [@Dayname] <> "Sunday"
),
[Date]
)
)
VAR _networkdays =
FILTER (
ADDCOLUMNS (
GENERATE (
_actualstartdate,
CALENDAR ( [@actualstartdate], [@actualstartdate] + 30 )
),
"@networkdays", NETWORKDAYS ( [@actualstartdate], [Date], 1, _holidaycalendar )
),
[@networkdays] = 3
)
RETURN
MINX (
FILTER ( _networkdays, Data[Delivery date] = EARLIER ( Data[Delivery date] ) ),
[Date]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
hi @Lyevon1
Try to
1) load the holidays as a table.
2) create a DateTable like this:
DateTable =
ADDCOLUMNS(
CALENDAR(DATE(2022,12,1), DATE(2023,2,1)),
"IsHolidayWeekend",
IF(
[Date] IN ALL(Holiday[Date])
|| WEEKDAY([Date],2) IN {6,7},
"Yes", "No"
)
)
3) add a column in your delivery table like this:
ExpectedDeliveryDate2 =
VAR _date = [DeliveyDate]
RETURN
MINX(
FILTER(
DateTable,
DateTable[IsHolidayWeekend] = "No"
&&DateTable[Date]>_date
),
DateTable[Date]
)+1
I tried and it worked like this:
Hi,
I am not sure if I understood your question correctly, but please check the below formula and the attached pbix file.
It is for creating a new column.
Expected result CC =
VAR _calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2023, 12, 31 ) ),
"@Dayname", FORMAT ( [Date], "dddd" )
)
VAR _holidaycalendar =
SUMMARIZE (
FILTER (
ADDCOLUMNS ( _calendar, "@holiday", [Date] IN DISTINCT ( Holiday[Holiday] ) ),
[@holiday] = TRUE ()
),
[Date]
)
VAR _actualstartdate =
ADDCOLUMNS (
Data,
"@actualstartdate",
MINX (
FILTER (
_calendar,
[Date] >= EARLIER ( Data[Delivery date] )
&& NOT ( [Date] IN _holidaycalendar )
&& [@Dayname] <> "Saturday"
&& [@Dayname] <> "Sunday"
),
[Date]
)
)
VAR _networkdays =
FILTER (
ADDCOLUMNS (
GENERATE (
_actualstartdate,
CALENDAR ( [@actualstartdate], [@actualstartdate] + 30 )
),
"@networkdays", NETWORKDAYS ( [@actualstartdate], [Date], 1, _holidaycalendar )
),
[@networkdays] = 3
)
RETURN
MINX (
FILTER ( _networkdays, Data[Delivery date] = EARLIER ( Data[Delivery date] ) ),
[Date]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi Mr Jihwan,
Quick question, if i have wish to have a separate table for the SLA, how should i go about doing that?
apology for the question if this is a straight forward ans.. i'm still new to BI.
Hi,
I added a new table into the file by using the below formula.
It is for creating a new table.
Please check if it suits your requirement.
Expected result Table =
VAR _calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2023, 12, 31 ) ),
"@Dayname", FORMAT ( [Date], "dddd" )
)
VAR _holidaycalendar =
SUMMARIZE (
FILTER (
ADDCOLUMNS ( _calendar, "@holiday", [Date] IN DISTINCT ( Holiday[Holiday] ) ),
[@holiday] = TRUE ()
),
[Date]
)
VAR _actualstartdate =
ADDCOLUMNS (
Data,
"@actualstartdate",
MINX (
FILTER (
_calendar,
[Date] >= EARLIER ( Data[Delivery date] )
&& NOT ( [Date] IN _holidaycalendar )
&& [@Dayname] <> "Saturday"
&& [@Dayname] <> "Sunday"
),
[Date]
)
)
VAR _networkdays =
FILTER (
ADDCOLUMNS (
GENERATE (
_actualstartdate,
CALENDAR ( [@actualstartdate], [@actualstartdate] + 30 )
),
"@networkdays", NETWORKDAYS ( [@actualstartdate], [Date], 1, _holidaycalendar )
),
[@networkdays] = 3
)
RETURN
GROUPBY (
_networkdays,
Data[Delivery date],
Data[Day Name],
"@expectedresultdate", MINX ( CURRENTGROUP (), [Date] )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you so much Mr Jihwan, this works!. appreciate so much for the advice!
@Lyevon1 , You need to create column with flag of weekend and holiday, and then follow an approch like
Column code is given in the comments
User | Count |
---|---|
52 | |
51 | |
20 | |
17 | |
16 |
User | Count |
---|---|
113 | |
45 | |
44 | |
28 | |
22 |