Calculate number of business days in current month and compare to total business days in the month

[datatbl123]

Hi,

I am wanting to show the amount of completed business days so far in the month as a count and also as a percentage. I only want to return the current month.

I am looking for a formula that will calculate the number of business days in current month so far that has occured (Monday-Friday, except Saturday-Sunday) and also will consider banking holidays (from a set list of dates that we can define, see below) and compare that count to the total of business days within the month.

E.g, if today is 12/13/2022, there has been 9 business days so far in the month (including the 13th) and there are a total of 21 business days in December 2022 (Christmas, December 25th being a banking holiday, 26th being the actual day off since it is observed).

Also would like to return the calculation as a percentage which I am sure we can just divide the 2 different counts. In example above, the percentage of business days completion on 12/13/2022 would be 9/21=43% of business days completed in the month so far.

List of banking Holidays to account for:

Christmas Day 2022: 12/26/2022 (observed since the 25th falls on a Sunday)

New Year's Day 2023: 1/2/2023 (observed since the 1st falls on a Sunday)
Martin Luther King Jr. Day 2023: 1/16/2023 (observed since the 15th falls on a Sunday)
Washington's Birthday (Presidents Day) 2023: 2/16/2023
Memorial Day 2023: 5/29/2023
Juneteenth National Independence Day 2023: 6/19/2023
Independence Day 2023: 7/4/2023
Labor Day 2023: 9/4/2023
Columbus Day 2023: 10/9/2023
Veterans Day 2023: 11/10/2023 (observed since the 11th falls on a Saturday)
Thanksgiving Day 2023: 11/23/2023
Christmas Day 2023: 12/25/2023

Thank you,

[Mikelytics · Accepted solution]

Hi @datatbl123 

Please try the following.

Hokiday Table for December

Measure 1 for total net working days

004 NETWORKDAYS of Month = 
var __Today = TODAY()
var __StartOfMonth = EOMONTH(__Today,-1)+1
var __EndOfMonth = EOMONTH(__Today,0)
var __WeekEndParameter = 1
var __HolidayDates = VALUES(Sup_Holidays[Dates])

RETURN

NETWORKDAYS(__StartOfMonth, __EndOfMonth, __WeekEndParameter,__HolidayDates)

Measure 2 for days until today (only difference is second parameter in NETWORKDAYS formula

004 NETWORKDAYS of Month until Today = 
var __Today = TODAY()
var __StartOfMonth = EOMONTH(__Today,-1)+1
var __EndOfMonth = EOMONTH(__Today,0)
var __WeekEndParameter = 1
var __HolidayDates = VALUES(Sup_Holidays[Dates])

RETURN

NETWORKDAYS(__StartOfMonth, __Today, __WeekEndParameter,__HolidayDates)

 Percentage measure

004 NETWORKDAYS of Month until Today (percentage) = 
[004 NETWORKDAYS of Month until Today] / [004 NETWORKDAYS of Month]

Result

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

[datatbl123]

This worked great! Thank you.

[Mikelytics]

Awesome! 🙂 Thank you for your feedback!

Best regards

Michael