Forum Discussion
Creating conditional column based on date, IsHoliday
Hi All,
I have a column of datetimes for all of 2019 and I want to create a conditional column 'IsHoliday' that is '1' if that date is a public holiday, and '0' otherwise.
I imagine for New Year's Day it would be something like:
IsHoliday = IF( 'Table'[Date] = DATE(2019,1,1), 1, 0)
However this only returns '1 'for one value but not all the the other datetimes on that date.
Any help would be appreciated.
Brian
Anonymous , weekend are easy, for public holday you might have use API, refer this power bi solution for that
Work Day = if(WEEKDAY([Date],2)>=6,0,1)
you can lso copy holday from other tables to date table
refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8Hi Anonymous ,
You need to get a list of holidays online, such as the following:
Establish the relationship between the calendar table and the holiday table
Then add a calculated column behind the calendar table
IsHoliday = IF( ISBLANK(RELATED('2019 Holiday'[Date]))=FALSE(), 1,0)Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous ,
You need to get a list of holidays online, such as the following:
Establish the relationship between the calendar table and the holiday table
Then add a calculated column behind the calendar table
IsHoliday = IF( ISBLANK(RELATED('2019 Holiday'[Date]))=FALSE(), 1,0)Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous , weekend are easy, for public holday you might have use API, refer this power bi solution for that
Work Day = if(WEEKDAY([Date],2)>=6,0,1)
you can lso copy holday from other tables to date table
refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8