Solved Solution: Calculate networkingdays - Multiple. non-working-days lists

[pinarello]

A standard function for calculating the net working days does not yet exist in Power Query. But since almost everyone can google meanwhile, the searcher will find what he is looking for very quickly. The enclosed folder also contains some references.

Of course, a list of non-working days can be assigned to the respective solutions in order to exclude holidays and other non-working days from the calculation.

However, I did not find a solution where it was possible to assign any number of lists with different non-working days. Furthermore, I did not like in the solutions found that lists of the individual days are generated from the respective date ranges in order to then exclude the weekends and the days off from these, even though the lists are always generated for only one data record, since the calculation of the net working days takes place in a function.

So I thought that it should be quite easy to calculate the working days without excluding the days off. And in fact this is quite simple. First the number of total days is determined and from this the number of full weeks multiplied by 2 is subtracted. Depending on the weekday start and weekday end, only 1, 2 or no day must be subtracted and we have the number of gross working days.

The next step is a table join with the holiday list with the key fields year and list name. After expanding the holidays, the next step is to insert a "1" in a new column if the holiday is within the respective period and then group the list again according to the original key and thereby determine the sum of the holidays.

The sum of the non-working days holidays is then still subtracted from the gross working days.

With regard to the runtime, periods spanning more than one year are divided beforehand among the years in question, and the list of days off is given the year as a key.

The net working days are calculated in the PQ once with internal auxiliary columns and once without auxiliary columns (column: Net wk days 1s).

In order to be able to check the results, the sample folder also contains a formula solution.

 

[pinarello]

With further tests I had to determine that after the last adjustment, with which I divided the from/to date ranges on years, I would have had to shift then also the from/to weekday determination downward. Since I had missed this, deviations of -2 to +2 days could occur for time periods spanning years.

In the current folder this is corrected and I have also optimized the internal process of the Power Query a bit. Furthermore, I have also outsourced the determination of the net working days to a function, as well as created queries with the functions of Imke Feldmann and Rick de Groot.

For all queries, it is now controlled via parameters whether days off should also be taken into account. Also via parameters it is controlled how many records should be output at the end, whereby this filter is only executed when all calculations are done and thus has no effect on the runtime. I have installed this filter only, in order to be able to upload the folder still.

I also created a folder with each of the functions of Imke Feldmann and Rick de Groot and at the end I made hangstopped runtime measurements.

Using the functions, the method I developed takes only half as long as Imke's and Rick's, if the free days are not considered. If the free ones are also considered in the calculation, my method is 10-20% slower, but has the advantage that a different list of free days can be referenced per data set.

But if the calculation is done directly in the query, then it takes only a quarter of the time. This is a result I did not expect. It will probably not be the normal case that calculations performed in a function are slower.

Furthermore, I noticed that Imke's function calculates one day less for about 2/3 of the data sets. However, I have not taken the trouble to determine the cause of these deviations.

 

[pinarello]

I was now able to find out why Imke's function partially calculated one day too few. It was because I found and used the version 1.0 instead of the version 3.1 in the net. The version 3.1 determines the expected results.

Here is the link to version 3.1

Date.Networkdays function for Power Query and Power BI –

Calculate net working days like in Excel with my new Date.Networkdays function for Power Query. For holidays, simply refefrence column of a calendar table

www.thebiccountant.com