Creating a measure that ignores a specific filter context

[SuddenClarity]

I'm trying to create measure that would ignore a certain filter context but retain all others. In this case, the one I'm trying to ignore is Year.

I have a fact table for my sales, which has a relationship to several dimension tables such as date, product, custom, etc. And now I want to create a measure that would show me the combined sales for all years so I wrote the below measure:

Measure = CALCULATE ( [Sales],  ALL ('Date') )

Then to test it out I created a matrix with the years on the rows and put [Sales] and my new measure on the columns but for some reason it's only showing me the sales for the most current year, see below:

I'm not sure what's going on there. Perhaps there's another filter at play that's causing this?

I've also tried CALCULATE ( [Sales], ALL('Date'[Year]) ) but no luck. 

[Greg_Deckler]

@SuddenClarity Use ALLEXCEPT instead of ALL, or use this instead: https://youtu.be/fH0VcV9Smow

ALLEXCEPT Without CALCULATE - Microsoft Power BI Community

[SuddenClarity]

@Greg_Deckler Thanks Greg, I'll check out those resources. Do you happen to know why my formula with the ALL function doesn't work? 

[Greg_Deckler]

@SuddenClarity Because ALL strips out all context, including the context that you want to keep. Hence, ALLEXCEPT, strip out all context except the ones that you want to keep. Or, you could use ALLSELECTED, that would keep all filters external to the visual and since Year is in the visual, that would go away. Or, or, you could potentially use REMOVEFILTERS to remove the Year filter.

[SuddenClarity]

@Greg_Deckler Thanks, this is kind of driving me nuts here. No matter what I try, I am still returning only the sales of the latest year, or 2022. For e.g. I tried:

Measure = CALCULATE (

                [Sales],

                ALLSELECTED('Date'[Year])

)

And I still end up with the same output as my screenshot above. Even though I know it should return the sum of [Sales] across all years in the matrix. 

Any idea what may be causing this behavior? 

[v-cgao-msft]

Hi @SuddenClarity ,

Can you show me the fields in the visual? Is your year field come from the date hierarchy? If that's the case, please try:

Measure = CALCULATE([Sales],ALL('Date'[Date].[Year]))

But it is recommended to use the Year function in the Date table to create a calculated column and use it in the visual.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[SuddenClarity]

@v-cgao-msft Thanks for getting back! Sorry I am not allowed to show all the fields but take a look at the screenshot below. The year doesn't belong to a heirarchy. Instead, I created a calculated table of all the possible dates between the max and min date in the data model. And then from those list of dates, I created some calculated columns for the month and year. 

One interesting thing I just noticed is that the matrix total is just showing the 2022 sales. Does this offer a hint as to what my problem may be? 

Also the sales figure is YTD. So the measure is essentially CALCULATE ( [Sales] , DATESYTD ('Date'[Date]))