Question on DAX

[hideakisuzuki01]

Hi,

I have this very simple table named "Sales",

And I am trying to add a calculated column that gives "Last Year Sales" and I dont know why one DAX formua works and the other one does not.

This one works:

LastYearSales2 =

VAR LastYear = 'Sales'[Year] -1

VAR LastYearSales = CALCULATE(

    SUM(Sales[SalesAmount]),

    FILTER('Sales','Sales'[Year] = LastYear))

RETURN LastYearSales

This one does not work:

LastYearSales =

VAR LastYear = 'Sales'[Year] -1

VAR LastYearSales = CALCULATE(

    SUM(Sales[SalesAmount]),

    'Sales'[Year] = LastYear)

RETURN LastYearSales

Even without using the FILTER function, I thought the DAX engine automatically adds the FILTER functions before it gets processed so I dont know why one works and the other does not.

[hideakisuzuki01]

oh, that does make sense.  Just when I thought I understood context transition, something like this happens and realize I still have a lot to learn. 

Thanks so much !

[DataInsights · Accepted solution]

@hideakisuzuki01,

This is due to context transition (see article below). LastYearSales overrides the filter context of only the Year column, leaving in place the other column filters that result from context transition. LastYearSales2 filters the entire table for the specified condition, and then uses that table filter in CALCULATE. If you add "ALL(Sales)" to LastYearSales, it removes the filters resulting from context transition and returns the correct result:

LastYearSales = 
VAR LastYear = Sales[Year] - 1
VAR LastYearSales =
    CALCULATE (
        SUM ( Sales[SalesAmount] ),
        Sales[Year] = LastYear,
        ALL ( Sales )
    )
RETURN
    LastYearSales

https://www.sqlbi.com/articles/understanding-context-transition-in-dax/ 

You can achieve the same result with SUMX:

LastYearSales SUMX = 
VAR vYear = Sales[Year]
VAR vTable =
    FILTER ( Sales, Sales[Year] = vYear - 1 )
VAR vResult =
    SUMX ( vTable, Sales[SalesAmount] )
RETURN
    vResult