Forum Discussion
Calculate the difference in values whenever year changes
Hello!
For every change in Store_ID, Metric_Name I need the difference of Goal_Value between every year and the respective next year.
Example: Store_ID = 1, Metric_Name = A, 2024 (vs. 2023) = 3%
I have the following table structure:
| Store_ID | Metric_Name | Goal_Value | Year |
| 1 | A | 77% | 2023 |
| 1 | A | 80% | 2024 |
| 1 | A | 88% | 2025 |
| 1 | B | 5% | 2023 |
| 1 | B | 8% | 2024 |
| 1 | B | 12% | 2025 |
| 2 | A | 60% | 2023 |
| 2 | A | 67% | 2023 |
| 2 | A | 72% | 2023 |
| 2 | C | 50% | 2023 |
| 2 | C | 40% | 2024 |
| 2 | C | 30% | 2025 |
A measure would be my prefered solution, but if it's a calculated column, that would be fine too.
Thank you in advance!
Fromit87 , it better to have a separate year table, join with your table and try
//Only year vs Year, not a level belowThis Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])or try like, Assume Goal_Value is Measure, using offset for previous year
LastYear = CALCULATE([Goal_Value], OFFSET(-1, ALLSELECTED(Table[Store_ID],Table[Metric_Name],Table[Year]), ORDERBY(Table[Year],asc),KEEP,PARTITIONBY(Table[Store_ID],Table[Metric_Name])))
1 Reply
Fromit87 , it better to have a separate year table, join with your table and try
//Only year vs Year, not a level belowThis Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])or try like, Assume Goal_Value is Measure, using offset for previous year
LastYear = CALCULATE([Goal_Value], OFFSET(-1, ALLSELECTED(Table[Store_ID],Table[Metric_Name],Table[Year]), ORDERBY(Table[Year],asc),KEEP,PARTITIONBY(Table[Store_ID],Table[Metric_Name])))