cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chudson Regular Visitor
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
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

chudson Regular Visitor
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.

 

Capture.PNG

ChrisMendoza Senior Member
Senior Member

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

@chudson,

 

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

 

3.png

 

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

ChrisMendoza Senior Member
Senior Member

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

@Greg_Deckler, that's nifty!

chudson Regular Visitor
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?

 

 

Capture.PNG

chudson Regular Visitor
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.  

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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