Remaining Business Days from TODAY

[ssmootbokf]

Goal: To caulcate "Average pace per day needed to hit goal" 

I built a Federal Holiday Table and created relationship between it and my "Master Date" Table. 

Next I calculated: Total work days = CALCULATE(COUNT('Master Date'[Master Date].[Date]),FILTER('Master Date','Master Date'[If work day]=1))

What i need now is "Remaining business days (M-F No Holidays), excluding today left in this month. 

I tried:

Work Day Left = COUNTROWS(FILTER(CALENDAR(TODAY(),EOMONTH(TODAY(),0)),WEEKDAY([Date],2)<6))

but it resulted in "18". Today is the 8th, so excluding today and Memorial day there are 16 business days remaining. 

I just can't seem to get it right! Help please!  

[edhans · Accepted solution]

This measure works.

Remaining Workdays = 
VAR LastDayOfCurrentMonth =
CALCULATE(
    ENDOFMONTH(Dates[Date]),
    FILTER(Dates,Dates[Month] = MONTH(TODAY()))
)
Return
CALCULATE(
    COUNTROWS(Dates),
    FILTER(Dates,Dates[Date] >= TODAY() && Dates[Date] <= LastDayOfCurrentMonth),
    FILTER(Dates,Dates[WorkDay] = TRUE())
) -1

There might be an shorter way to do this, but I used a true Date table, so I needed it to figure out the last day of the current month regardless of the filter context, so that is what the LastDayOfCurrentMonth variable is figuring out.

My date table also has the Workday field as a logical true/false, not 1,0, but you could change my last FILTER() statement to be = 1 vs = TRUE().

The '-1' at the end is because you didn't want to include today. It calculated 15 for me today, which is correct since today is the 9th. 31 - 9 = 22 - 6 weekend days = 16 - 1 Memorial Day holiday = 15.

If you want to take a look at my PBIX file it is here. You would just relate the date in my date table to the date in your data table, and it would still work. I used a public holiday calendar, to the number of holidays is unrealistic for most companies, but that can be edited on your side for your particular company.

[ssmootbokf]

I had to make a few tweaks to my table but this totally worked! Thank you so much!