Forum Discussion

pbiuser0000's avatar
pbiuser0000
Regular Visitor
5 years ago
Solved

Calculate % Previous Scenario

Hello all, 

 

I'm struggling on how to build my measure

 

Here's the context, I have one source as follows : 

 

ScenarioProductSales
Actual 2018X10
Actual 2018Y20
Actual 2019X20
Budget 2020X100
Budget 2020Y150
Forecast1 2020X200
Forecast1 2020Y250
Forecast2 2020X210
Forecast2 2020Y260
Actual 2020X100
Actual 2020Y200

 

I would like in a dynamic way when I select a scenario as filter calculate the variation between the scenario selected and the previous one.

 

FYI the order of the scenario is Budget -> Forecast1 -> Forecast2 -> Actual

E.g :

I select Actual 2020, my measure calculate the variation between Actual 2020 and Forecast2 2020.

I select Forecast2 2020, my measure calculate the variation between Forecast2 2020 and Forecast1 2020.

....

I select Budget 2020, my measure returns null as there's no previous scenario.

Same for Actual 2018 and Actual 2019

 

Thank you for your help.

 

 

 

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi pbiuser0000 ,

     

    It is recommended that you split Scenario column, which is helpful for DAX calculation.

    Custom sort for the items in column Scenario in the query editor.Then create a column like below:

    Column = 
var last_ = CALCULATE(FIRSTNONBLANK('Table'[Sales],1),FILTER(ALLEXCEPT('Table','Table'[Scenario.2],'Table'[Product]),EARLIER('Table'[Custom])='Table'[Custom]+1))
return IF(ISBLANK(last_),BLANK(),'Table'[Sales]-last_)

     

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi pbiuser0000 ,

     

    It is recommended that you split Scenario column, which is helpful for DAX calculation.

    Custom sort for the items in column Scenario in the query editor.Then create a column like below:

    Column = 
var last_ = CALCULATE(FIRSTNONBLANK('Table'[Sales],1),FILTER(ALLEXCEPT('Table','Table'[Scenario.2],'Table'[Product]),EARLIER('Table'[Custom])='Table'[Custom]+1))
return IF(ISBLANK(last_),BLANK(),'Table'[Sales]-last_)

     

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • pbiuser0000's avatar
      pbiuser0000
      Regular Visitor
      5 years ago

      Dear Liang,

       

      Thank you a lot for your help !

       

      It works perfectly well.

       

      Regards.