cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Prodigy
Post Prodigy

Difference between date and time columns (excluding public holidays)

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. 

1 ACCEPTED SOLUTION
Super User III
Super User III

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


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

1 REPLY 1
Super User III
Super User III

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


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors