How to exclude the holidays and weekends from number of days by using M code in power bi

[Shuwyyyy]

Hi All,

How to exclude the holidays and weekends from number of days calculations  by using M code in power bi.

In this scenario i got how to exclude the weekends but after that how can i add the logic to exclude the public holidays .

Please assist me on this. Thanks in advance!!

 Regards,

Pratima

[Tahreem24]

@Shuwyyyy ,

You can refer the below link with accepted solution to meet your requirement.

https://community.powerbi.com/t5/Desktop/How-exclude-holidays-from-networking-days/m-p/785656

Please don't forget to hit THUMBS UP and Accept this as a solution if it helps you!

[rajulshah]

Hello @Shuwyyyy,

You can create a calculated column in Dates table which has values as "Weekend", "Weekday" & "Holiday".

And then in measure you can filter out only those who has "Weekday" value.

[v-alq-msft · Accepted solution]

Hi, @rajulshah 

Based on your description, you may create a table of holidays called 'Holidays', and has a single column called Date. 

Then you may create a new query in the Query Editor, go to 'View' ribbon, click 'Advanced Editor'.

You may input codes as below.

let
    Networkdays = (StartDate as date, EndDate as date) as number =>
let
    Source = List.Dates,
    #"Invoked FunctionSource" =
    if StartDate <= EndDate then
        Source(StartDate,Duration.Days(EndDate-StartDate)+1, Duration.From(1))
    else
        Source(EndDate, Duration.Days(StartDate-EndDate)+1,Duration.From(1)),
    #"Converted to Table" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Inserted Day of Week" = Table.AddColumn(#"Converted to Table", "DayOfWeek", each Date.DayOfWeek([Column1]), type number),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Day of Week",{"Column1"},Holidays,{"Date"},"Holidays",JoinKind.LeftOuter),
    #"Expanded Holidays" = Table.ExpandTableColumn(#"Merged Queries", "Holidays", {"Date"}, {"HolidaysDate"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Holidays", each ([DayOfWeek] <> 0 and [DayOfWeek] <> 6)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [HolidaysDate] = null),
    Custom1 = if StartDate <= EndDate then Table.RowCount(#"Filtered Rows1") else Table.RowCount(#"Filtered Rows1") * (-1)
in
    Custom1
in
    Networkdays

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shuwyyyy]

Hi v-alq-msft ,

Thank you so much for the detail explanation.It helped me to get the solution i was looking for.

Thanks and Regards,

Pratima