Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jaltoft
Resolver I
Resolver I

Network days excluding weekends and holidays calendar

Can anyone please help?

 

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.

RefDays1Days2Wanted new column date diff without workday
15121501/02/201811/05/2019 
151515105/08/201812/06/2019 
151515120/08/201820/12/2020 
155151511/09/201920/12/2020 

 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@jaltoft , refer this for Power Query /M

https://www.thebiccountant.com/2020/02/20/date-networkdays-function-for-power-query-and-power-bi/

 

DAx -https://www.sqlbi.com/articles/counting-working-days-in-dax/

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

User5231
Helper II
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

2021-01-30 14_59_56-Window.png

View solution in original post

5 REPLIES 5
User5231
Helper II
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

2021-01-30 14_59_56-Window.png

amitchandak
Super User
Super User

@jaltoft , refer this for Power Query /M

https://www.thebiccountant.com/2020/02/20/date-networkdays-function-for-power-query-and-power-bi/

 

DAx -https://www.sqlbi.com/articles/counting-working-days-in-dax/

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@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?

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

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.