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

       

       

      • TomMartens's avatar
        TomMartens
        Super User
        8 years ago

        Hey,

         

        thanks for providing sampledata, here you will find my solution a sample pbix file.

        I extracted a sequence number from the column Visitname using the Extraxt Text Function "Text Between Delimiters", see the Applied Steps in the Query Editor:

         

        This is necessary due to the fact that some kind of ordering (a sequence) is mandatory to determine a previous value.

         

        Then i used a DAX statement to create a calculated column:

        Previous SiteRateScore = 
var curSubjectNumber = 'score'[SubjectNumber]
var curSequence = 'score'[Sequence]
var prevSequence = 
CALCULATE(
    MAX('score'[Sequence])
    ,FILTER(ALL(score)
        ,'score'[SubjectNumber] = curSubjectNumber && 'score'[Sequence] < curSequence
    )
)
return
LOOKUPVALUE('score'[SiteRaterScore],score[SubjectNumber],curSubjectNumber,score[Sequence],prevSequence)

         

        Here is a screenshot:

         

         

        If this solution doesn't meet your requirement, please explain why, this will help me and others to provide another solution and also explain how a sequence within each SubjectNumber can be derived from the data you provided.

         

        Regards

        Tom

         

        If this solves your question, please mark this post as answer, and also consider to give Kudo to this post, at least for honoring the time others spent trying to provide an answer.