allexcept for circular ref but now can't filter correctly

[crobaseball]

I'm using CALCULATE in a couple of calculated columns and they created a circular reference.  I found the article explaining the prob and know I need to use ALLEXCEPT to eliminate the reference.  The prob is that ALLEXCEPT is then breaking the filter I'm trying to use.

here are the orig steps i'm trying to take.  If existing in a vacuum, both work fine:

MTDSales = CALCULATE(SUM('03- Raw Data View'[Sessions]), '03- Raw Data View'[IsCurrentMonth] = "Yes")

LstYrMTDSales = CALCULATE(SUM('03- Raw Data View'[Sessions]), '03- Raw Data View'[IsLastYrMonth] = "Yes")

I then tried to change LstYrMTDSales to this:

LstYrMTDSales = CALCULATE(SUM('03- Raw Data View'[Sessions]), '03- Raw Data View'[IsLastYrMonth] = "Yes", ALLEXCEPT('03- Raw Data View','03- Raw Data View'[Date]))

I've tried every single column in this table and the filtered values do not give me the correct sum.  Does anyone have any guidance on what to ALLEXCEPT on to be able to make my filter work correctly?

[v-kelly-msft · Accepted solution]

Hi  @crobaseball ,

It is because that the 2 filters conflict.You'd better use below expression:

LstYrMTDSales = CALCULATE(SUM('03- Raw Data View'[Sessions]),FILTER(ALL('03- Raw Data View'), '03- Raw Data View'[IsLastYrMonth] = "Yes"&&'03- Raw Data View'[Date]=MAX('03- Raw Data View'[Date])))

Or you could use below expression with "Allexcept":

LstYrMTDSales = CALCULATE(SUM('03- Raw Data View'[Sessions]), ALLEXCEPT('03- Raw Data View','03- Raw Data View'[Date],'03- Raw Data View'[IsLastYrMonth]))

Then add a slicer and choose '03- Raw Data View'[IsLastYrMonth]="Yes":

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[crobaseball]

What I realized is that I was doing a calculated column and should have been using a measure, which eliminates the circular reference.