Forum Discussion

TomMartens's avatar
TomMartens
Super User
8 years ago

Hey,

 

I couldn't resist and created sample data by myself, here you will find a pbix file. The sheet "Not yet validated" is recreating your requirement. The table "GroupOfStates" represents your data that you use in your post. I also created a table "Sequence", this table represents the sequence of categorical Events (meaning a column that is not represented by a numerical or date data type).

 

This measure in the table "GroupOfStates" calculates the Previous Value:

PreviousStateValue = 
SUMX('GroupOfStates'
    ,var currentGroup ='GroupOfStates'[Group]
    var currentState = 'GroupOfStates'[State]
    var previousState = 
    CALCULATE(FIRSTNONBLANK('Sequence'[Predecessor],1)
        ,'Sequence'[State] = currentState
    )
    return
    CALCULATE(
        SUM('GroupOfStates'[Amount])
        ,FILTER(ALL('GroupOfStates')
            ,'GroupOfStates'[Group] = currentGroup && 'GroupOfStates'[State] = previousState
        )
    )
)

This screenshot looks quite similar to your requirement:

 

Hopefully this is what you are looking for.

 

Regards

Tom

  • chotu27's avatar
    chotu27
    Post Patron
    8 years ago

    TomMartens

     

    Yes, But your calculation is not working for me

    • TomMartens's avatar
      TomMartens
      Super User
      8 years ago
      So it would be helpful if you explain why my calculation doesn't work for you, and it seems that it would be much easier for me to pursue if you provide sample data.

      Regards
      Tom
      • chotu27's avatar
        chotu27
        Post Patron
        8 years ago

        Hi,

        Please check sample data here  .  

         

        Columns need for calculation check below image