Exclude Weekends and Public Holidays in DATEDIFF Measure using PowerBI-CodePudding

Im trying to exclude Weekends and Public Holidays for the PowerBi Measure below,

First Report = IF(abs(DATEDIFF(C[CreatedDate].[Date],C[Earliest Date max per ID],DAY) 1)<=2,"OnTime","Late")

Basically what the Measure should check if the difference between these dates are equals to or less than a day excluding weekends and public holidays. Im not sure how to modify this Measure in a way that it excludes weekends and public holiday. I've tried creating calendar table but not sure how to proceed from there. Would highly appreciate if someone could help me out

CodePudding user response：

Create a table, name it Public_Holiday & enter Public Holidays dates manually or maybe from excel or any other source. Crete a blank query in query editor and paste this M code in advance editor. I am assuming Sat & Sunday to be excluded.

(StartDate as date, EndDate as date) as number =>
let
List.Dates = List.Dates(StartDate, Number.From(EndDate-StartDate) 1, #duration(1,0,0,0)),
RemoveWeekends = List.Select(List.Dates, each Date.DayOfWeek(_,Day.Monday)<5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, Public_Holiday),
CountDays = List.Count(RemoveHolidays)
in
CountDays

Call this function anytime you need to get working days in query editor.

CodePudding user response：

You can try like this

VAR allDates= DATESBETWEEN ( 'Date'[Date], StartDate, EndDate )
-- VAR allDates = GENERATESERIES ( StartDate, EndDate , 1 ) -- If you don't have a Calendar table 
                                                            -- and you need to check all dates in period.
                                                            -- It can create another column name for Filter()
VAR allDatesExceptHolidays = EXCEPT(allDates,VALUES('Holydays'[Date]))
VAR allDatesExceptHolidaysNoWeekend=
        FILTER(
            allDatesExceptHolidays 
            ,WEEKDAY([Date],2)<6
        )
VAR DaysQty = COUNTROWS(allDatesExceptHolidaysNoWeekend)

RETURN 
    DaysQty