0

Date.Networkdays function for Power Query and Power BI

Today I’m going to share my custom NETWORKDAYS function for Power Query with you that uses the same syntax than its Excel-equivalent.

NETWORKDAYS function

This function’s first 3 parameters work just like the Excel function and there is a 4th parameter that allows adjusting the day on which the week shall start:

  1. Start as date
  2. End as date
  3. optional holidays as list of dates
  4. optional number to change the start of the week from Monday (default: 1) to any other day (2 would mean that the week starts on Tuesday instead)

The function comes with a UI that lets you first choose a table containing the holidays and then choose the column with the holiday dates.

Date.Networkdays function for Power Query and Power BI

UI for NETWORKDAYS function for Power Query

Date.Networkdays function for Power Query and Power BI

Select date column for NETWORKDAYS function

But you can also type in the list of holidays in manually. Therefore leave the optional parameter blank if you use it through the UI and edit the formula afterwards like so:

fnNETWORKDAYS ( StartDate, EndDate, {#date(2020, 1, 1) {#date(2020,12,25)} ), adding all necessary dates into the 3rd parameters list.

The Code

Twists

If your holidays don’t sit in a dedicated table but in separate column of a calendar table like so:

Holiday as a column within a complete Calendar table

I’d recommend to reference that table, filter only holidays and then reference it’s data column like mentioned before.

Enjoy & stay queryious 😉

The post Date.Networkdays function for Power Query and Power BI appeared first on The BIccountant.

Read the Full Article here: >The BIccountant

Erich Dejesus

Leave a Reply

Your email address will not be published. Required fields are marked *