Forum Discussion
Understanding a Measure
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.
2 Replies
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.
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.
