Forum Discussion

Euro0681's avatar
Euro0681
Icon for Helper II rankHelper II
3 years ago
Solved

ALLEXCEPT still filtering

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's avatar
    v-jianboli-msft
    3 years ago

    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.

1 Reply

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.