Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I've seen a few other somewhat similar threads to calculate this using DAX, but nothing including how to exclude specific holidays.
I have a separate date table that has "Weekday/Weekend" column, and a "Holiday/Not a Holiday" column.
I currently have a measure that counts the total number of working days.
Workdays Left in Month = COUNTROWS( FILTER( CALENDAR( TODAY(), EOMONTH(TODAY(), 0) ), WEEKDAY([Date], 2) < 6 ) )But how would I calculate the number of days left in the month, including weekdays, but EXCLUDING holidays as marked in my calendar table? Any help is greatly appreciated.
Solved! Go to Solution.
Something like:
Workdays Left in Month = COUNTROWS(
EXCEPT(
FILTER(
CALENDAR(
TODAY(),
EOMONTH(TODAY(), 0)
),
WEEKDAY([Date], 2) < 6
),
SELECTCOLUMNS(
FILTER(
'Dates',[IsHoliday] = 1
),
"Date",[Date]
)
)
hi @Anonymous
For your case, I would suggest you use this simple formula to create a measure
Measure =
COUNTROWS (
FILTER (
'Calendar',
'Calendar'[Date] >= TODAY ()
&& 'Calendar'[Date] <= EOMONTH ( TODAY (), 0 )
&& 'Calendar'[Holiday_Flag] = "Not a Holiday"
&& WEEKDAY ( 'Calendar'[Date], 2 ) < 6
)
)
If you still have the problem please share your sample pbix file and your expected output.
Regards,
Lin
Try something like this with calendar having holiday
Workdays Left in Month =
var _st = date(year(today()),month(today()),1)
var _ed = date(eomonth(today())
return =
sumx(
FILTER(date, date[date]>=today() && date[date]<=_ed),date[working Day])
It has working day field : https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0
Something like:
Workdays Left in Month = COUNTROWS(
EXCEPT(
FILTER(
CALENDAR(
TODAY(),
EOMONTH(TODAY(), 0)
),
WEEKDAY([Date], 2) < 6
),
SELECTCOLUMNS(
FILTER(
'Dates',[IsHoliday] = 1
),
"Date",[Date]
)
)
Thank you so much for this. Once I corrected the error on my end it all worked out. I've just never really worked with the EXCEPT function before.
@Anonymous No worries, I wasn't trying to yell at you BTW, I was just trying to add emphasis. I will remember to use bold next time!
@Greg_Deckler When I attempt to write that desk as pertinent into my report, I run into an issue after the SELECTCOLUMNS / FILTER portion after we account for holidays.
You have
"Date",[DATE] but it's giving me errors when I attempt to enter this last part in.
What is the error? You want to get a single column of the same name in both your tables when using EXCEPT. See this as well:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109
Maybe I'm just not understanding what exactly needs to go into the "date", [date] section at the very end. When i enter verbatim "Date", 'Calendar'[date])) That just gives me a (BLANK) result in my card visual.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |