Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Understanding a Measure

Im sure this is very simple but can anyone help me understand this measure below?   WorkingDaysInMonth = VAR _date = MAX(DimDate[Date]) RETURN     CALCULATE(         [TotalWorkingDays],     ...
  • gogolgupta786's avatar
    1 year ago

    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.