Forum Discussion
Measuring change in values on specific dates
Hi all,
I'm trying to use 'VAR' to do measure the change between 2 numbers on specific dates.
Basically, (old-new)/old - between 2 specific points of time that won't move, or change. They are fixed dates and fixed figures. This data is static. (the end result showing a -/+ % change).
Table is built like this:
| Sector Name | Value | Date |
| A | 1 | 2019 |
| B | 2 | 2019 |
| C | 3 | 2019 |
| D | 4 | 2019 |
A | 5 | 2050 |
B | 6 | 2050 |
C | 7 | 2050 |
D | 8 | 2050 |
I want to know the change from VALUE between 2019 and 2020, for each SECTOR (will use a filter selection).
Code I've attempted:
Hi lbendlin ,thanks for the quick reply, I'll add more.
Hi Jelbeanie ,
Try this
Value % difference from 2019 b = VAR base = CALCULATE( SUM('Employment by Industry'[Value]), 'Employment by Industry'[Year] IN { "2019" }) Var future = CALCULATE( SUM('Employment by Industry'[Value]), 'Employment by Industry'[Year] IN { "2050" }) Var subtraction = base - future Var result = DIVIDE(subtraction, base) RETURN IF ( NOT ISBLANK(result),result)Best Regards,
Wenbin Zhou
3 Replies
Hi lbendlin ,thanks for the quick reply, I'll add more.
Hi Jelbeanie ,
Try this
Value % difference from 2019 b = VAR base = CALCULATE( SUM('Employment by Industry'[Value]), 'Employment by Industry'[Year] IN { "2019" }) Var future = CALCULATE( SUM('Employment by Industry'[Value]), 'Employment by Industry'[Year] IN { "2050" }) Var subtraction = base - future Var result = DIVIDE(subtraction, base) RETURN IF ( NOT ISBLANK(result),result)Best Regards,
Wenbin ZhouThank you so much - this worked! Great learnings. Thank you 🙂
Consider using Visual Calculations instead. They have a concept of PREVIOUS (both for rows and columns)
