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

[chudson]

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.

[chudson]

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?

[chudson]

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.  

[Greg_Deckler]

See my Net Work Days Quick Measure:

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

[ChrisMendoza]

@Greg_Deckler, that's nifty!

[chudson]

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.

[ChrisMendoza · Accepted solution]

@chudson,

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].