Calculate % Previous Scenario

[pbiuser0000]

Hello all, 

I'm struggling on how to build my measure

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

Scenario	Product	Sales
Actual 2018	X	10
Actual 2018	Y	20
Actual 2019	X	20
Budget 2020	X	100
Budget 2020	Y	150
Forecast1 2020	X	200
Forecast1 2020	Y	250
Forecast2 2020	X	210
Forecast2 2020	Y	260
Actual 2020	X	100
Actual 2020	Y	200

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.

[V-lianl-msft · Accepted solution]

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]

Dear Liang,

Thank you a lot for your help !

It works perfectly well.

Regards.