Forum Discussion

bgonen6899's avatar
bgonen6899
Regular Visitor
1 year ago
Solved

Variances between Scenarios

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 sc...
Need Help
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago

    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

Kedar_Pande's avatar
Kedar_Pande
Icon for Super User rankSuper User
1 year ago

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's avatar
bgonen6899
Regular Visitor
1 year ago

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