cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Network days excluding weekends and holidays calendar

What I want to do is create a nextwork days column within my data (Table.cases) which will show me the correct amount of days on each item number (Ref) between Date1 and Date2.

The example shown is how the data looks like within Table.cases, I also have a Calendar table in which I created which will flag workday which will flag up if the day is not a holiday or a weekend as 1.

I have so far used a date diff and then tried to remove the sum on the days as 1 in the calendar but it want correct as they were not associating.

 Ref Days1 Days2 Wanted new column date diff without workday 151215 01/02/2018 11/05/2019 1515151 05/08/2018 12/06/2019 1515151 20/08/2018 20/12/2020 1551515 11/09/2019 20/12/2020

2 ACCEPTED SOLUTIONS
Super User
Helper II

I found a solution I haven't seen used for anyone interested.

If you have a DateTable that takes note of holidays and weekends by date, then for any give date, we can calculate the difference between dates using some sneaky logic.

So if you have a date table as below screenshot shown. Create a Key No. 1 column

Next, create an index column using a running total formula.

calculate(sum('Date'[Key]), all('Date'[Date]), 'Date '[Date]<'earlier('Date'[Date]), 'Date'[Day] á "Weekday", 'Date'[Holiday] ? "N")
This will create a running index that repeats the index number in weeks and holidays.
For the two given dates, you search for the indexes and subtract, add 1 if you want to include numbers of days between including the start and end date.
Días entre -LOOKUPVALUE('Date'[Index], 'Date'[Date], Table[Last Date]) - LOOKUPVALUE('Date'[Index], 'Date'[Date], Table[First Date])+1

5 REPLIES 5
Helper II

I found a solution I haven't seen used for anyone interested.

If you have a DateTable that takes note of holidays and weekends by date, then for any give date, we can calculate the difference between dates using some sneaky logic.

So if you have a date table as below screenshot shown. Create a Key No. 1 column

Next, create an index column using a running total formula.

calculate(sum('Date'[Key]), all('Date'[Date]), 'Date '[Date]<'earlier('Date'[Date]), 'Date'[Day] á "Weekday", 'Date'[Holiday] ? "N")
This will create a running index that repeats the index number in weeks and holidays.
For the two given dates, you search for the indexes and subtract, add 1 if you want to include numbers of days between including the start and end date.
Días entre -LOOKUPVALUE('Date'[Index], 'Date'[Date], Table[Last Date]) - LOOKUPVALUE('Date'[Index], 'Date'[Date], Table[First Date])+1

Super User
Resolver I

@amitchandak im not sure this answers my issue? Can the function be used in the calculated column? As the original question?

Thanks for the reply. Can you help?

Resolver I

Hello @amitchandak ive now had a look at your Dax link. That proved a perfect solution thanks.

Resolver I

Hello that looks good but can I use the function in the new calculated column? I have never used them within PowerBi previously.