Different way of filter in calculation for Last year

[RajCZ]

What should be difference

Dax1 =

var Yr=max('Date'[Year ])-1

Return CALCULATE(sum(Sales[Sale]), 'Date'[Year ]=yr)

Vs

Dax2= CALCULATE(sum(Sales[Sale]),'Date'[Year ]-1)

Can somebody please explanin what is difference between Dax1 and Dax2.

I always use Dax1 type expression, but today was trying like in Dax2, Dax2 giving me strange output, value is much higer.

Thanks

[RajCZ]

Yes, I know I can update like that, but I am just wondering what is value that I see in DAX2, may be I can find a sceanrio in future where that type of calculation be useful.

Thanks

[v-kongfanf-msft]

Hi @RajCZ ,

In DAX2, you directly use CALCULATE to filter the SUM(Sales[Sale]) measure where the 'Date' table's year is equal to the maximum year in your 'Date' table minus 1. This means it's trying to filter for the year Yr - 1 where Yr is the maximum year in your 'Date' table.

CALCULATE(SUM(Sales[Sale]), 'Date'[Year] - 1)

Implicit Context in DAX2: DAX2 subtracts 1 from the 'Date'[Year] column without specifying a condition for CALCULATE. It filters for the year that is one less than the maximum year in the entire 'Date' table, not the year before the maximum year in context.

In short, there are very few scenarios in which dax2 would be written in such a way that it would produce unexpected results

Best Regards,
Adamk Kong

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

[v-kongfanf-msft]

Hi @RajCZ ,

The difference between the two DAX formulas lies in how they handle the year filtering, and the behavior of Dax2 can lead to unintended results.

• Dax1 formula correctly calculates the sum of sales for the previous year by defining the year in a variable and then using that variable in the CALCULATE function.
• Dax2 does not work as intended. The condition 'Date'[Year] - 1 is not a valid filter expression in the context of CALCULATE. Instead, it will lead to an incorrect result because it doesn't directly filter the year. Instead, it likely performs some unexpected operation, which can lead to incorrect and higher results.

You can modify your formula like below, it can get the same result:

Dax1 = 
VAR Yr = MAX('Date'[Year]) - 1
RETURN CALCULATE(SUM(Sales[Sale]), 'Date'[Year] = Yr)

Dax2 = 
CALCULATE(
    SUM(Sales[Sale]),
    'Date'[Year] = MAX('Date'[Year]) - 1
)

Best Regards,
Adamk Kong

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