Calculating Working Days Left in Month excluding holidays

[Anonymous]

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. 

[v-lili6-msft]

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

[amitchandak]

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

[Anonymous]

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. 

[Greg_Deckler]

@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!

[Anonymous]

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

[Greg_Deckler]

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

[Anonymous]

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]

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

[Greg_Deckler]

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.

[Greg_Deckler]

Ok. So is your last part like:

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