Forum Discussion
Remaining Business Days from TODAY
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()) ) -1There 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.
Community ChampionThis 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())
) -1There 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.
I had to make a few tweaks to my table but this totally worked! Thank you so much!