Creating conditional column based on date, IsHoliday

[Anonymous]

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

[amitchandak · Accepted solution]

@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

[Anonymous · Accepted solution]

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]

Awesome mate, cheers

[amitchandak · Accepted solution]

@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