Forum Discussion

M_SBS_6's avatar
M_SBS_6
Helper V
3 years ago

Variance Year on Year

Hi, 

I have created a measure that I hoped would calculate the variance between current year and previous year but my calculation below is taking the variance current year vs the first year.

 

Example 

Year.   Value diff

2018.   0.39 0.00

2019.   0.22 -0.17

2020.   0.29 -0.10

 

Each value is based on the change from current year vs the first year (2018). What I need is this 

 

Year value diff

2018 0.39 0.00

2019 0.22- 0.17

2020 0.29 0.07

 

This is my current code. Any idea what I need to amend to make this work, please?

 

Diff=

Var __baseline_value=

Calculate([value], [year] in {"2018"}),

 

Var __measure_value = [value]

Return

If(not isblank(__measure_value), __measure_value - __baseline_value)

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    M_SBS_6 , Create a separate table with distinct years and join it with the year of your table (Say Date)

     

    The create measures like example

    //Only year vs Year, not a level below

    This 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 ])

     

     

    Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s