Forum Discussion

trailblazer2022's avatar
trailblazer2022
New Member
3 years ago
Solved

Dynamic Baseline for Percentage Difference

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!

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

     

2 Replies