Forum Discussion

tasmiaa's avatar
tasmiaa
Icon for Advocate I rankAdvocate I
6 years ago

Measure showing previous scenario

Account Value Scenario

A10A1
A5A2
B3A1
A5A3
A4A1

I have a slicer that allows users to pick their Scenario (A1, A2, A3) and a measure that sums Value at the given Scenario for Account A.

Ex: User selects Scenario A1 and gets Sum of A = 14.

Problem: I need to create a measure that allows the user to compare the Sum of A to the previous Scenario.

  • Ex: User selects Scenario A1 and gets a Sum of A = 14 compared to Previous Scenario (A3) = 5. I have a measure that returns the previous Scenario (A1 > A3, A2 > A1, A3 > 2).

Question: What is the DAX to do this?

  • I've tried Previous Scenario = Calculate(Calculate(SUM(Value), Filter(Table, Table[Account] = "A"), Filter(Table, Table[Scenario] = [Previous Scenario]) but this does not work because the previous scenario gets filtered out before I can apply the filter.

Thanks!

Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    tasmiaa sorry it is not clear what is the logic to get previous scenario. Can you provide more details?

    • tasmiaa's avatar
      tasmiaa
      Icon for Advocate I rankAdvocate I
      6 years ago

      Sure!

       

      Previous Scenario = IF(SelectedValue(Scenario) == "A1", "A3", IF(SelectedValue(Scenario) == "A2", "A1", If(SelectedValue(Scenario) == "A3", "A2")))

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

        tasmiaa here is the measure for previous scenario value

         

        Previous Scenario = 
VAR __selectedValue = SELECTEDVALUE ( PRev[Type] ) 
VAR __prev = SWITCH ( __selectedValue, "A1", "A3", "A2", "A1", "A3", "A2" )
RETURN
CALCULATE ( [Total Amt], PRev[Type] = __prev )

         

        change table and col name as per your mode, [Total Amt] is simple sum of amount column