topic Total Working Days in a month based on slicer selection in DAX Commands and Tips

Total Working Days in a month based on slicer selection

collinq — Mon, 18 Jan 2021 18:28:06 GMT

Hi all,

I have attached a link to my pbix sample file (here - https://tinyurl.com/y522q5l7 )

I want to select a date range and retrieve the number of working days in each of those represented months. So, if the date slicer were 8/1/20-8/15/20 that is the month of August. The month of August has 21 working days in it (for my specific calendar anyway). Therefore, I need the result of "21".

If the date slicer is 8/1/20-9/15/20 that is August and September. August has 21 working days and September has 21 working days in my calendar file so I need to have a result of "42".

In my DateDim I have whether or not the date is a working day.

I am having trouble getting the DAX right to give me that total working number of all the months and would be most appreciative if somebody could help me out.

Thanks!

Re: Total Working Days in a month based on slicer selection

dedelman_clng — Mon, 18 Jan 2021 20:30:44 GMT

Hi collinq -

This might not be that elegant, but give it a try:

NumWorkDays = VAR __MinDt = CALCULATE ( MIN ( DateTab[Date] ), ALLSELECTED ( DateTab[Date] ) ) VAR __MaxDt = CALCULATE ( MAX ( DateTab[Date] ), ALLSELECTED ( DateTab[Date] ) ) VAR __MinMth = MONTH ( __MinDt ) VAR __MinYr = YEAR ( __MinDt ) VAR __MaxMth = MONTH ( __MaxDt ) VAR __MaxYr = YEAR ( __MaxDt ) VAR __MinDate = DATE ( __MinYr, __MinMth, 1 ) VAR __MaxDate = EOMONTH ( DATE ( __MaxYr, __MaxMth, 1 ), 0 ) RETURN CALCULATE ( COUNTROWS ( FILTER ( ALL ( DateTab ), DateTab[Date] >= __MinDate && DateTab[Date] <= __MaxDate && DateTab[IsWorkDay] ) ) )

(IsWorkDay is a Boolean on the Date table marking Sat-Sun as false, so ignoring holidays)

Should be able to span years, not just months.

Hope this helps

David

Re: Total Working Days in a month based on slicer selection

collinq — Tue, 19 Jan 2021 15:01:53 GMT

Hi dedelman_clng ,

Elegant or not, that is beautiful to me! Thanks so much!!! I was definitely going down a much different and eventually ineffective solution. Thanks for your time and thanks for your effort!