Dynamic Baseline for Percentage Difference

[trailblazer2022]

Hi, I have the following formula:

Revenue % difference from 1H 2019 = 
VAR __BASELINE_VALUE =
  CALCULATE(
    [Revenue],
    'Revenue by Product'[Period] IN { "1H 2019" },
    ALL('Revenue by Product'[Period ID])
  )
VAR __MEASURE_VALUE = [Revenue]
RETURN
  IF(
    NOT ISBLANK(__MEASURE_VALUE),
    DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE)
  )

I would like to make the _BASELINE VALUE dynamic i.e. changing "1H 2019" to "2H 2019" to "1H 2020" and so on. The period has a corresponding period ID i.e. 1H 2019 = 1, 2H 2019 = 2 and so on. 

Is it possible as I want to calculate the percentage difference between two consecutive bars i.e. 1H 2019 and 2H 2019, 2H 2019 and 1H 2020 and so on. Appreciate any help please!

[Anonymous · Accepted solution]

Hi @trailblazer2022 ,

You will need a dim table like below:

column1   column2

1H 2019    1

2H 2019    2

1H 2020    3

2H 2020    4

Then use column1 as x-axis and replace { "1H 2019" } to selectedvalue([ column2])+1/-1

Best Regards,

Jay

[lbendlin]

You are describing the basic feature of filter context.  

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523