Difference between date and time columns (excluding public holidays)

[Dunner2020]

Hi there,

I have two date and time columns (let say 'Start date time' & 'End date time') and they have values like '1/01/1900 12:00:00 AM'. I also got a table that contains a list of public holidays. Holidays contain the date of holiday without a year. For example, the Christmas holiday value would be December 25th. 

I want to create a measure that calculates the difference between 'Start date time' and 'End date time' in Days and exclude the public holidays. I am not sure how to exclude the public holidays from the list, specially when public holiday does not have year. Any help would be really appreciated. 

[edhans · Accepted solution]

You have to have a date table to do this properly. If your date table has an IsHoliday column then you simply count the rows in the date table between your start/end dates that are not holidays. Then this measure would work:

Non-Holiday Day Count = 
VAR varStartDate =
    MAX( 'Date Ranges'[Start Date] )
VAR varEndDate =
    MAX( 'Date Ranges'[End Date] )
VAR varDayCount =
    CALCULATE(
        COUNTROWS( Dates ),
        FILTER(
            ALL( Dates ),
            Dates[Date] >= varStartDate
                && Dates[Date] <= varEndDate
                && Dates[IsHoliday]
                    = FALSE()
        )
    )
RETURN
    varDayCount

See my PBIX file here for how the tables work. You can see them in the Transform Data section before they are loaded to DAX.

Creating a Dynamic Date Table in Power Query

Slight edit: If you also want to exclude weekends, use this. My Day of Week column has 0-6 for every day, where 0 is Sunday, 6 is Saturday, so this only includes days 1-5.

Non-Holiday Day Count = 
VAR varStartDate =
    MAX( 'Date Ranges'[Start Date] )
VAR varEndDate =
    MAX( 'Date Ranges'[End Date] )
VAR varDayCount =
    CALCULATE(
        COUNTROWS( Dates ),
        FILTER(
            ALL( Dates ),
            Dates[Date] >= varStartDate
                && Dates[Date] <= varEndDate
                && Dates[IsHoliday]
                    = FALSE()
                && Dates[Day of Week] IN GENERATESERIES(1,5)
        )
    )
RETURN
    varDayCount