ALLEXCEPT still filtering

Euro0681 — Wed, 21 Dec 2022 22:34:33 GMT

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

Re: ALLEXCEPT still filtering

v-jianboli-msft — Thu, 22 Dec 2022 02:25:11 GMT

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.

topic Re: ALLEXCEPT still filtering in DAX Commands and Tips

ALLEXCEPT still filtering

Re: ALLEXCEPT still filtering