Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
Dear Liang,
Thank you a lot for your help !
It works perfectly well.
Regards.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
105 | |
79 | |
71 | |
66 |
User | Count |
---|---|
141 | |
107 | |
100 | |
82 | |
74 |