Skip to main content
cancel
Showing results for 
Search instead 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

Reply
M_SBS_6
Helper V
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)

1 REPLY 1
amitchandak
Super User
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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.