Hi there,
I am looking to calculate the remaining business days in the 2020 calendar year. I’m looking to subtract weekends and holidays out as well. Below are the key measures I have so far. Any guidance is greatly appreciated.
There are many ways you can do this - here is one way
totalNoOfROws = COUNTROWS(Filter(All(Dates[Date]), Dates[Date]>=Date(Year(TODAY()), MONTH(TODAY()), Day(TODAY())) && Dates[Date]<=Date(Year(Today()),12,31) && WEEKDAY ( Dates[Date], 2 ) >=2 && WEEKDAY ( Dates[Date], 2 ) <=6 ))
note that i have my week starting monday - so weekday for sunday = 1 and weekday for saturday = 6
excluding it give me the no of workweek
if you have a Date calendar in your solution that captures Holidays, weekdays and weekends the solution will be even simpler.
Hopefully the attached query gets you what you need.
let me know if you have any further question
Hi Jackie,
Welcome to the forum!
@C9411010 is correct there are many ways to calculate this, few other options are:
Remaining Workdays =
CALCULATE(
COUNTROWS( Dates ),
FILTER( ALL(Dates),
Dates[Year] = YEAR( TODAY() ) &&
Dates[IsWorkingDay] = TRUE() &&
Dates[IsHoliday] =FALSE() &&
Dates[Date] > TODAY()
)
)
or
Remaining Working days =
COUNTROWS(
CALCULATETABLE( Dates,
Dates[Year] = YEAR( TODAY() ),
Dates[IsWorkingDay] = TRUE(),
Dates[IsHoliday] =FALSE(),
Dates[Date] > TODAY()
)
)
In the attached file you’ll find a Date table (marked as date table) which also includes a Holiday list.
I hope this is helpful. Here’s my sample file.
Remaining working days.pbix (206.1 KB)
2 Likes
Thank you to both of you! I am still getting an error due to the text values. Please help me to understand what I am missing here, I feel like it looking me right in the face.
I’d guess that your Dates[Year] is formatted as a text - just turn that into a number and it should be sorted.
That was it! Thank you so much for your help!