cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## 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)

Super User

@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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.