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.
I have a Working Days column that produces True / False in my Calendar that works perfectly fine:
Working Days = NOT WEEKDAY('Date'[Date]) IN {1,7}
I would like to incorporate my Public Holidays calendar to the column.
I have tried adding RELATED (see below) but that doesn't work
Working Days = NOT WEEKDAY('Date'[Date]) IN {1,7} &&
RELATED('bank-holidays'[Public Holiday])
Does anyone know how to combine them?
Thanks
Solved! Go to Solution.
Try this calculated column in your Date table. It requires a 1:* relationship between Date and bank-holidays.
Working Days =
NOT WEEKDAY ( 'Date'[Date] ) IN { 1, 7 } && ISBLANK ( COUNTROWS ( RELATEDTABLE( 'bank-holidays' ) ) )
Proud to be a Super User!
The two expressions are evaluated independently. The first one works as described (check if Date is a weekend). The second one checks if the date in the current row of the Date table exists in bank-holidays table; if it doesn't, then it's considered a working day (assuming it's not a weekend). The assumption is that bank-holidays contains only holidays. Hope that helps.
Proud to be a Super User!
Try this calculated column in your Date table. It requires a 1:* relationship between Date and bank-holidays.
Working Days =
NOT WEEKDAY ( 'Date'[Date] ) IN { 1, 7 } && ISBLANK ( COUNTROWS ( RELATEDTABLE( 'bank-holidays' ) ) )
Proud to be a Super User!
Can you briefly explain how this works please?
My understanding is that NOT produces a list of FALSE values if the Date is a Weekend (not a work day), then this list is used as Lookup to the Bank Hoilday table where all NONBLANKS (public holidays) are also counted as FALSE - the 2 are then combined.
Is that correct - does it make sense?
The two expressions are evaluated independently. The first one works as described (check if Date is a weekend). The second one checks if the date in the current row of the Date table exists in bank-holidays table; if it doesn't, then it's considered a working day (assuming it's not a weekend). The assumption is that bank-holidays contains only holidays. Hope that helps.
Proud to be a Super User!
yes thank you, much appreciated!
That works perfectly - thank you!
Covering 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 |
---|---|
96 | |
95 | |
82 | |
71 | |
64 |
User | Count |
---|---|
115 | |
105 | |
95 | |
79 | |
72 |