Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou 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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |