Question about displaying previous year data over current year data

[QuantamPulse]

I have an issue displaying previous year data over current year data for a date range selected in a slicer. 

I use the following to set up the measures and have them set to display on a line graph. 

Sakes Data Count Current Year = COUNTROWS(requestTable)

Sales Data Count Previous Year = CALCULATE(
    COUNTROWS(requestTable),
    FILTER(
        ALL(requestTable),
        YEAR(requestTable[Date]) = YEAR(MAX(requestTable[Date])) - 1
        && FORMAT(requestTable[Date], "MMM") = FORMAT(MAX(requestTable[Date]), "MMM")
    )
)

And this works fine until another filter is applied, for example, a Needs slicer. The current year data adjusts, but the previous year data does not. I have tried chaning ALL to ALLSELECTED instead, but them the entire line on the chart for the previous year disappears. 

Not sure what I am doing wrong here but any help would be appriciated. 

[Greg_Deckler · Accepted solution]

@QuantamPulse Maybe try ALLEXCEPT

[Greg_Deckler · Accepted solution]

@QuantamPulse Maybe try ALLEXCEPT

[QuantamPulse]

Do you happen to know if you can specify multiple columns in the ALLEXCEPT function? The documentation on it is a little unclear. 

The documentation shows the following context but I'm not sure why there would be a comma inside of a bracket?

ALLEXCEPT(Table, Column1 [,Column2]...)

[v-zhengdxu-msft · Accepted solution]

Hi @QuantamPulse 

Certainly, you can understand it as:

The columns from the specified table for which you want to keep filters. Filters on all other columns will be removed.

The primary use case of ALLEXCEPT is to create calculations within a particular context while disregarding filters applied to other columns. This is especially handy in scenarios where you need to perform calculations within a specific dimension or attribute of your data, ignoring filters on other dimensions.

Here is a blog about the understanding of context:

Understand the Filter Context and How to Control i... - Microsoft Fabric Community

Links of ALLEXCEPT():

Demystifying the ALLEXCEPT Function in DAX | by Andrew Hubbard | Microsoft Power BI | Medium

The Power BI ALLEXCEPT function (datascientest.com)

Best Regards

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

[QuantamPulse]

Never used ALLEXCEPT before. Worked perfectly. Set the column in the parameters to the column that I needed filtering by and it worked like a charm. 

Thanks!