cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 :

 

Ben-Jy_0-1597328628945.png

 

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


Ben-Jy_1-1597328739566.png

 

Hope you can help me 🙂

 

Thank you !

 

Benjamin

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX
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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
Highlighted
Super User IX
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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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.

Helpful resources

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 Kudoed Authors