Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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].
Proud to be a Super User!
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?
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.
See my Net Work Days Quick Measure:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362
@Greg_Deckler, that's nifty!
Proud to be a Super User!
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.
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].
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |