Variances between Scenarios

[bgonen6899]

I have several scenarios and when I choose the two scenarios the selected values are showing on the table correctly (see table below).

The variances should be most recent scenario Minus the Older scenario for example Budget 2024 minus Actual 2023

I am using the following DAX but I keep getting blanks:

Scenario Variance =

VAR SelectedScenario1 = SELECTEDVALUE(Scenarios[Scenario], BLANK())

VAR SelectedScenario2 = SELECTEDVALUE(Scenarios[Scenario], BLANK())

RETURN

    IF(

        AND(NOT ISBLANK(SelectedScenario1), NOT ISBLANK(SelectedScenario2)),

        SWITCH(

            TRUE(),

            SelectedScenario1 = "Forecast 10" && SelectedScenario2 = "Budget 2024", [Forecast 10] - [Budget 2024],

            SelectedScenario1 = "Plan 2025" && SelectedScenario2 = "Forecast 10", [Plan 2025] - [Forecast 10],

            SelectedScenario1 = "Plan 2025" && SelectedScenario2 = "Budget 2024", [Plan 2025] - [Budget 2024],

            SelectedScenario1 = "Plan 2025" && SelectedScenario2 = "Actual 2023", [Plan 2025] - [Actual 2023],

            BLANK()

        ),

        BLANK()

    )

[Bibiano_Geraldo]

Hi @bgonen6899 ,

Please try the bellow DAX:

Scenario Variance =
VAR SelectedScenario1 = SELECTEDVALUE(Scenarios[Scenario], BLANK())
VAR SelectedScenario2 = SELECTEDVALUE(Scenarios[Scenario], BLANK())

RETURN
    IF(
        AND(NOT ISBLANK(SelectedScenario1), NOT ISBLANK(SelectedScenario2)),
        SWITCH(
            TRUE(),
            SelectedScenario1 = "Forecast 10" && SelectedScenario2 = "Budget 2024", [Forecast 10] - [Budget 2024],
            SelectedScenario1 = "Plan 2025" && SelectedScenario2 = "Forecast 10", [Plan 2025] - [Forecast 10],
            SelectedScenario1 = "Plan 2025" && SelectedScenario2 = "Budget 2024", [Plan 2025] - [Budget 2024],
            SelectedScenario1 = "Plan 2025" && SelectedScenario2 = "Actual 2023", [Plan 2025] - [Actual 2023],
            BLANK()
        ),
        BLANK()
    )

Thank you

[bgonen6899]

Thank you Bibianco,

I like the logic but for some reason all the combinations return blanks

[Kedar_Pande · Accepted solution]

@bgonen6899 

Here’s an updated DAX measure to handle this:

Scenario Variance =
VAR SelectedScenarios = VALUES(Scenarios[Scenario])
VAR Scenario1 = MAXX(SelectedScenarios, Scenarios[Scenario])
VAR Scenario2 = MINX(SelectedScenarios, Scenarios[Scenario])
VAR Scenario1Value =
SWITCH(
TRUE(),
Scenario1 = "Forecast 10", [Forecast 10],
Scenario1 = "Plan 2025", [Plan 2025],
Scenario1 = "Budget 2024", [Budget 2024],
Scenario1 = "Actual 2023", [Actual 2023],
BLANK()
)
VAR Scenario2Value =
SWITCH(
TRUE(),
Scenario2 = "Forecast 10", [Forecast 10],
Scenario2 = "Plan 2025", [Plan 2025],
Scenario2 = "Budget 2024", [Budget 2024],
Scenario2 = "Actual 2023", [Actual 2023],
BLANK()
)
RETURN
IF(
AND(NOT ISBLANK(Scenario1Value), NOT ISBLANK(Scenario2Value)),
Scenario1Value - Scenario2Value,
BLANK()
)

Give this a try, and let me know how it works!

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[bgonen6899]

By the way, I ran all the possible scenarios are reflecting correct variances.

However, Forecast 10 vs. Budget 2024 reflects opposite variances.

I wonder if there is a way to rank the scenarios meaning:

1.Actual 2023  (because it is the oldest scenario)

2.Budget 2024 (it is the second oldest scenario)

3."2024 Forecast 10" (normally is completed 3rd quarter of year 2024)

4."Plan 2025" most recent scenario

[bgonen6899]

Hello Kedar

This works perfectly.

Thank you so much.

[DataNinja777]

Hi @bgonen6899 ,

My observation regarding your variance analysis is that it is not possible to simultaneously select "Forecast 10" and "Budget 2024." As a result, your output is displaying as blank. I recommend creating specific scenarios for each variance comparison, such as "Forecast 10 vs Budget 2024." Alternatively, for a more dynamic approach, you can make the variance scenarios respond to two slicer selections.

A common error in comparisons across multiple future plans arises when the year dimension is included in the analysis. Filtering the year dimension to 2024 will inadvertently exclude data from other years, such as 2025, 2026, and 2027.

Best regards,

[amitchandak]

@bgonen6899 , If they from same table you should use min and max

Scenario Variance =
VAR SelectedScenario1 = Minx(allselected(Scenarios), Scenarios[Scenario]))
VAR SelectedScenario2 = Maxx(allselected(Scenarios), Scenarios[Scenario]))

or use two slicer one with disconnected table 

Compare Categorical Data Using Slicers - Compare two Brands: https://youtu.be/exN4nTewgbc