cancel
Showing results for
Did you mean:
Regular Visitor

## Calculation in a Date Table to Return the day of the month excluding weekends or holidays

Is there a way to created a calculated column to show the day # in a month excluding weekends/holidays in a Date table.

So for example, I have 30 days in June 2018 and the day #'s of the month range from 1,2,3,...30.  I want a calculation to exclude weeknds/holidays but still count the day so the month.  In the case of June 2018, it would be a total of 21 days excluding weekends and holidays so I want want each day to range from 1-21 if they are not a weekend or holiday.

6 REPLIES 6
Super User

## Re: Calculation in a Date Table to Return the day of the month excluding weekends or holidays

See my Net Work Days Quick Measure:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Regular Visitor

## Re: Calculation in a Date Table to Return the day of the month excluding weekends or holidays

Hi Greg,

This isn't working.  I don't want to create a calculated measure.  I want this to be a calculated column in my dimensiondate table.

If you see the below column I basically want a column next to that giving the number of day in the month and not including any weekends or holidays.  Example,

July 1 = 1, July 2 = 2, July 3 = 3, July 4th = 0, July 4 = 4, etc but only adding up if they aren't a holiday or weekend.

Senior Member

## Re: Calculation in a Date Table to Return the day of the month excluding weekends or holidays

the Day number can be taken from the Query Editor with a couple of clicks:

You could then write a simple IF in DAX like:

`isWeekDay = IF( WEEKDAY(dCalendar[DateKey],3) >= 5, FALSE(), TRUE())`

Then you could do your SUM off of [isWeekDay].

I'm not aware of a formula or function to capture [isHoliday] so you'll likely need to have an additional table for that information.

So a simple table 'Holiday' with [Date]. In my example, 5/28/2018 is a holiday.

Relate the 'dCalendar' to 'Hloliday'

in 'dCalendar' you can create a calculated column [isHoliday]:

`isHoliday = ISNUMBER( RELATED(Holiday[Date]))`

then you could do another calculated column [isWorkDay]:

`isWorkDay = IF(dCalendar[isWeekDay] = FALSE() || dCalendar[isHoliday] = TRUE(), 0, 1) `

Then you could do a SUM off of [isWorkDay].

Senior Member

## Re: Calculation in a Date Table to Return the day of the month excluding weekends or holidays

@Greg_Deckler, that's nifty!

Regular Visitor

## Re: Calculation in a Date Table to Return the day of the month excluding weekends or holidays

Let's ignore holidays for now and look at just the month of June 2018.

Basically, I want the calculated column to equal the value of "WorkingDayNumber" from my picture below based on the date.

It will be a 0 if Sat/Sun and then will add the running total for that month up to 21 days.  I want to do this for an entire date table for all months.  Can anyone help with a calculated column formula?

Regular Visitor

## Re: Calculation in a Date Table to Return the day of the month excluding weekends or holidays

Was anyone able to look into for figure out a way to get this calculated column for working day number for each date in a date table?  The reason I want this is for graphical purposes to have the working day show up instead of the day of the month.

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,743)