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

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

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

Thank you Bibianco,

 

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