Total Working Days in a month based on slicer selection

[collinq]

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!

[dedelman_clng · Accepted solution]

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

[collinq]

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!