I have a calculated measure MFILTER that is a max date to filter out data after the desired reporting range in the table DATA. The DATA table that has the following fields:
| Employee | Client | Date | MonthEnd | Qty |
The following measure is being used in table to show prior month quantity by dept & employee in a matrix:
PMTotal =
VAR
PM = eomonth([MFILTER],-1)
VAR
CY = year([MFILTER])
RETURN
calculate(sum(DATA[QTY]),
filter(DATA,
DATA[monthend]= PM
&&year(DATA[monthend])=CY
)
)
The measure is correct for active employees with hours, but if there was an employee in the dept with old records PMTotal is populating with a sum of the last records that employee has (i.e. ignoring the filter).
If I change the last part of the code to:
Year(DATA[MonthEnd]) = CY year(today())
The filter works and the employees with old data are excluded.
Any help/input on why the year filter is not working when referencing the VAR CY is appreciated.
