Filtering Matrix with Calculated Measure

[connork]

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.

[v-yuezhe-msft]

@connork,

What formula do you use to create MFILTER measure? Could you please share sample data of your table and post expected result here ?

Regards,
Lydia Zhang