Variance Column

[Anonymous]

I have a matrix that looks like this

company             2022               2023            2024

orders                   2342              1234            5678

sales                       2342              1234            5678

income                   2342              1234            5678

I want to add a column that is year by year variance, so it should look like this

company             2022               2023            2024             Variance

orders                   2342              1234            5678                    x

sales                       2342              1234            5678                  y

income                   2342              1234            5678               z

I want the variance column to be filled in with the orders vs last year, sales vs last year, and income vs last year. How would I be able to do this?

[Arul]

@Anonymous ,

Which value are you expecting in place of x,y,z? Please provide any logic or formula.

[Anonymous]

variance of orders from 2023 to 2024 in x, variance of sales from 2023 to 2024 in y, variance of income from 2023 to 2024 in z

[Arul]

@Anonymous ,

Correct if me if I'm wrong,

2024[orders]-2023[orders] likewise for others.

[aduguid · Accepted solution]

Give this measure a try 

Yearly Variance = 
VAR CurrentYear = SELECTEDVALUE('Date'[Year])
VAR PreviousYear = CurrentYear - 1
VAR Metric = SELECTEDVALUE('Metrics'[Metric])

RETURN
SWITCH(
    TRUE(),
    Metric = "Orders", [Orders],
    Metric = "Sales", [Sales],
    Metric = "Income", [Income]
) - 
CALCULATE(
    SWITCH(
        TRUE(),
        Metric = "Orders", [Orders],
        Metric = "Sales", [Sales],
        Metric = "Income", [Income]
    ),
    'Date'[Year] = PreviousYear
)