Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
This one does not work:
Solved! Go to Solution.
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
Proud to be a Super User!
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 !
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
Proud to be a Super User!
User | Count |
---|---|
91 | |
84 | |
65 | |
62 | |
58 |
User | Count |
---|---|
148 | |
112 | |
95 | |
81 | |
71 |