Forum Discussion
Question about displaying previous year data over current year data
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.
QuantamPulse Maybe try ALLEXCEPT
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.
4 Replies
- Greg_Deckler
Community Champion
QuantamPulse Maybe try ALLEXCEPT
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!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]...)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.
