Forum Discussion
Calculating difference from previous year by area
Hi, I wonder if anyone can help me with what I was hoping would be a relatively common calculation, but I've so far failed to find a solution.
I need to calculate the difference in [Value] from the previous year, with the calculation resetting with the area name. An example of the data structure is shown below. I am trying to get PowerBI to calculate the 'Difference from previous year' column to get the values shown.
| Area | Year | Value | Difference from previous year |
| Area 1 | 2015 | 5001 | |
| Area 1 | 2016 | 6200 | 1199 |
| Area 1 | 2017 | 4980 | -1220 |
| Area 1 | 2018 | 2634 | -2346 |
| Area 1 | 2019 | 5284 | 2650 |
| Area 1 | 2020 | 6114 | 830 |
| Area 1 | 2021 | 7213 | 1099 |
| Area 1 | 2022 | 6003 | -1210 |
| Area 2 | 2015 | 1984 | |
| Area 2 | 2016 | 2103 | 119 |
| Area 2 | 2017 | 2115 | 12 |
| Area 2 | 2018 | 2301 | 186 |
| Area 2 | 2019 | 2286 | -15 |
| Area 2 | 2020 | 2199 | -87 |
| Area 2 | 2021 | 2311 | 112 |
| Area 2 | 2022 | 2385 | 74 |
| Area 3 | 2015 | 7150 | |
| Area 3 | 2016 | 7185 | 35 |
| Area 3 | 2017 | 7233 | 48 |
| Area 3 | 2018 | 7345 | 112 |
| Area 3 | 2019 | 7534 | 189 |
| Area 3 | 2020 | 7684 | 150 |
| Area 3 | 2021 | 7699 | 15 |
| Area 3 | 2022 | 7742 | 43 |
Any help gratefully received!
3 Replies
Hi,
To work with time intelligence function, we must have a date/calendar table. Make sure to have it created and relationship is created. Additionally, use the Year column(hierarchy) from the Date column of Date/Calendar Table
Next, you create a previous year value/data as:
Previous Year =VAR PREVIOUS=CALCULATE(SUM(Table[Value]),PREVIOUSYEAR('Calendar'[Date].[Date]))RETURN IF(ISBLANK(PREVIOUS),0,PREVIOUS)Second Measure:Difference = SWITCH(TRUE(),[Previous Year]=0,BLANK(),CALCULATE(SUM(Table[Value])-[Previous Year)Thank you both so much for replying.
I couldn't get the solution by Kaviraj11 to work (presumably my fault), but the code supplied by ThxAlot worked a treat and I've been able to apply it to my real-life data. Excellent stuff!
