cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Fill a column indicating if the day is a working day

Hello,

I have a table with all the dates of the year and I would like to fill in a column indicating if the day is a holiday.
To do so, I could not directly download a calendar, however I have a table containing one line per holiday with two columns indicating the start and end date of this holiday (we sometimes make a bridge when the holiday is a Thursday for example).

With this last table, I would need to fill the column of the date table, indicating if the day is a public holiday (with a '0') or a working day ('1').

I had already posted a post with a similar problem but this time I really need to fill this column.

Here is what the table with all the dates looks like (there are already '1' for working days and '0' if is a week-end day :

And here is table with the holidays (there are some duplicates, but it will be filter anyway)

Hope you can help me 🙂

Thank you !

Benjamin

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX

## Re: Fill a column indicating if the day is a working day

@Ben-Jy , Try a  new column like

new column =
var _new =if(countx(filter(holiday, date[date]>=holiday[start] && date[date]>=holiday[end] ),holiday[start])+0>0,0,1)
return
if([work day] =0 || _new =0 ,0,1)

[work day] is column where you have 1 for work day and 0 for weekend

Proud to be a Super User!

2 REPLIES 2
Highlighted
Super User IX

## Re: Fill a column indicating if the day is a working day

@Ben-Jy , Try a  new column like

new column =
var _new =if(countx(filter(holiday, date[date]>=holiday[start] && date[date]>=holiday[end] ),holiday[start])+0>0,0,1)
return
if([work day] =0 || _new =0 ,0,1)

[work day] is column where you have 1 for work day and 0 for weekend

Proud to be a Super User!

Highlighted
Frequent Visitor

## Re: Fill a column indicating if the day is a working day

@amitchandak perfect, it works. I understand now the formula and how it works, it will help me for further calculation.

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors