Measuring change in values on specific dates

[Jelbeanie]

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: 

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 =

    CALCULATE((Var base - var future)

Var result =

    DIVIDE(var subtraction, var base)

RETURN

IF ( not isblank(var result)

Can someone please advise?

thank you! 🙂 

[Anonymous · Accepted solution]

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

[Jelbeanie]

Thank you so much - this worked! Great learnings. Thank you 🙂

[lbendlin]

Consider using Visual Calculations instead. They have a concept of PREVIOUS (both for rows and columns)