Calculating Working Days in a Month by Date

[Anonymous]

Hi,

I'm trying to create a measure (or calculated column) that gives me the number of working days in a month for each date. So, for example, this measure would return 18 for each day in April 2019. I have a date table with the following columns: date [in MM/DD/YYYY], month number, month name, calendar year, day number, and IsWorkingDay [which returns 1 if the day is a weekday or 0 if it is not].

I tried using a measure:

WorkingDaysInMonth =
CALCULATE (
    SUM ( 'Date'[IsWorkingDay] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[CalYear] = SELECTEDVALUE ( 'Date'[CalYear] )
            && 'Date'[MonthNum] = SELECTEDVALUE ( 'Date'[MonthNum] )
    )
)

but this is only returning the same result as IsWorkingDay for each date in a matrix.

[v-yuta-msft]

@Anonymous ,

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

Regards,

Jimmy Tao

[v-yuta-msft]

@Anonymous ,

You may forget to add a condition in the filter statement:

WorkingDaysInMonth =
CALCULATE (
    SUM ( 'Date'[IsWorkingDay] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[CalYear] = SELECTEDVALUE ( 'Date'[CalYear] )
            && 'Date'[MonthNum] = SELECTEDVALUE ( 'Date'[MonthNum] )
            && 'Date'[IsWorkingDay] = 1
    )
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.