ALLEXCEPT still filtering

[Euro0681]

I'm having an issue with ALLEXCEPT

I've created a measure (displayed on the Left table) that calculates Sales Amount for the Previous Year.
The Table on the right shows the same values using a visual filter to filter the table to the previous year (Validation Purposes).
The numbers are correct the issue I'm having is Whenever i add a filter to the table on the left It also filters my measure, I want my measure to retain the same values at all times. Can anyone help?

Measure = 
Calculate ( 
               SUM('Table'[Sales Amount]),
               'Table'[Year] = MAX('Table'[Year])-1,
               AllExcept('Table','Table'[ID]
)

When I Start to filter by 'Table'[Date] It Changes my Numbers, and I believe the reason is because of the filter for Year because when I Filter by 'Date' the 'MAX'[Year] changes causing the calculation to change. So I dont belive the problem is with allexcept, I need a way for the previous year to always remain the same so my calculation stays correct

 

[v-jianboli-msft · Accepted solution]

Hi @Euro0681 ,

Based on your description, I have created a simple sample:

Please try:

Measure = 
Calculate ( 
               SUM('Table'[Sales Amount]),
               'Table'[Year] = MAXX(ALL('Table'),[Year])-1,
               AllExcept('Table','Table'[ID]
))

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.