Understanding a Measure

[Anonymous]

Im sure this is very simple but can anyone help me understand this measure below?

WorkingDaysInMonth =

VAR _date = MAX(DimDate[Date])

RETURN

    CALCULATE(

        [TotalWorkingDays],

        FILTER(

            ALL(DimDate),

            DimDate[Year] = YEAR(_date) && DimDate[MonthNum] = MONTH(_date)

        )

[gogolgupta786 · Accepted solution]

This measure ensures that whenever you select a date (or a report runs for a certain period), it finds the total number of working days for that month, ignoring any other filters that may affect the calculation.

1) VAR _date = Max(DimDate[Date]) ---> You'e assigning Max Date from the DimDate dimension to the variable _date
2) Calculate Working Days for the Month:

CALCULATE([TotalWorkingDays], ...) → This retrieves the total working days but applies filters to make sure it only considers the selected month.
The FILTER(ALL(DimDate), DimDate[Year] = YEAR(_date) && DimDate[MonthNum] = MONTH(_date)) part:
ALL(DimDate) → Ignores any existing filters on the date table to check all dates.
DimDate[Year] = YEAR(_date) & DimDate[MonthNum] = MONTH(_date) → Only keeps the dates that match the year and month of the selected date.

Some suggestions: 

• Instead of MAX(DimDate[Date]), consider using SELECTEDVALUE(DimDate[Date]) to handle cases where multiple dates exist or are selected or nothing is selected.
• The use of ALL(DimDate) removes all existing filters on the date table, if that's intended then its fine, else try encapsulating it with KEEPFILTERS to avoid removal of filter context.

[Thaumaturgist]

This measure first captures the date that is currently being used in the visual or calculation context by storing MAX(DimDate[Date]) in the variable _date. It then uses the CALCULATE function to compute [TotalWorkingDays], but it replaces any existing date filters with a new filter that only keeps rows in DimDate matching the year and month of _date. This ensures the measure returns the total working days for the entire month and year associated with the selected date, rather than just a single day or any other filtered period.