Network Days Between Dates

[Craigc3814]

I am trying to calculate network days between dates in PQ. I created a customer calendar parameter and removed weekends and holidays from the tables. I then created an index column starting from 1 that numbers my days with the idea that I would look up the end date number then the start date number and subtract the 2 giving me the number of days between the dates.

Ran into a problem, sometimes the start date is a weekend so when I lookup the value there is nothing there as I filtered it out of my calendar table. Any suggestions?

 

[James006]

Hello,

Why are you using the Excel Networkdays function which is design to calculate the number of days between tow dates ...

HTH

 

[Craigc3814]

Hello,

Why are you using the Excel Networkdays function which is design to calculate the number of days between tow dates ...

HTH

I'm not, I'm trying to replicate it in PQ

 

[James006]

Sorry read your post too quickly ...

To replicate Networkdays in Power Query :

https://www.powerquery.training/portfolio/networkdays/

HTH

 

[Craigc3814]

This solution does not work, as mentioned in my post the start date is sometimes weekends.

If the start date is a weekend and I have removed all weekends from the calendar table I cannot then subtract the days between the end date and the start date because the start date no longer exists.

It also does not address holidays.

I need to somehow have PQ understand that if work comes in on a weekend or holiday move the start date to the next network day.

 

[Craigc3814]

Here was my solution

I created a calendar table using calendar parameter and building the table in PQ.

I built a holiday table and added any holidays or random days I did not want included to this table.

I then merged the two tables but only brought in non matching rows from the calendar table (This removed/removes dates added to my holiday table)

I then filtered out all 0's and 6's (Sundays and Saturdays) I then created an index column called day count starting from 1 that numbered each date that my calendar table has in it.

I named the table Calendar Final

I went to my table that had my start and end dates (invoice table) and I filtered my invoice table start date from oldest to newest

I then Merged the Start date with the calendar table

I brought in all rows from my invoice table and only matching from my calendar table.

I expanded the day count column only and called it Start Date Day

This showed me the number from the calendar table associated with the start date but the issue I had was if the start date was a weekend or holiday it returned null.

I then did a FILL UP transform because really if the start date was a weekend the start date should have been moved to the next week days number.

I then did the exact same thing for end date and called it End Date Day

I filtered the end date from oldest to newest

merged again with the calendar table only bringing in the day count

BUT instead of filling up I filled down this time because if work was completed on a Saturday technically the company gets credit for it being done on Friday

I was then able to easily subtract the End Date Day from the Start Date Day and it gave me how many network days have passed.

It is a bit of a burden considering in Excel you can just use the formula network days but its functional and there was little to no custom coding other than the calendar parameter to create my calendar. Everything else was done through the toolbars.

PQ RULES!!!