Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculating Working Days Left in Month excluding holidays

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. 

Ship_Days_Total = CALCULATE(COUNTROWS(
'Calendar'),
FILTER('Calendar', 'Calendar'[WeekdayWeekend] = "Weekday"),
FILTER('Calendar','Calendar'[Holiday_Flag] = "Not a Holiday"))
I'm then using relative date filtering to just show the max possible working days in a month as of now. But, we set a sales target every month, and they want a KPI that shows dynamically what we need to average in sales each day to hit that target. 

I've seen another DAX formula from this forum for calcuating the number of weekdays left in a month: 

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. 
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

11 REPLIES 11
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

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

Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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. 

 
Anonymous
Not applicable

@Greg_Deckler 

Ship_Days_Remaining = COUNTROWS(
EXCEPT(
FILTER(
CALENDAR(
TODAY(),
EOMONTH(TODAY(),0)),
WEEKDAY([Date],2)<6)
,SELECTCOLUMNS(
FILTER(
'Calendar','Calendar'[Holiday_Flag] = "Not a Holiday")
,"date", 'Calendar'[Date])))
That's what I've got so far, It's not giving me errors, but I think it's something with the last line that's throwing it off with the "date" part. 

No, you want the ones that ARE holidays in your second table. EXCEPT returns everything from the first table that is NOT in the second table. That's why I had IsHoliday = 1 or true in the original example provided.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Ok. So is your last part like:

SELECTCOLUMNS(
FILTER(
'Calendar',[Holiday_Flag] = 1
),
"Date",[Date]
)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.