Forum Discussion

14 Replies

  • 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

      • 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
  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago
    Hey,

    to compare score with previous score just create this measure (assuming both are measures):
    Compare=
    SUMX('yourtablename'
    ,IF([score] > [previous score], 1, 0)
    )

    If one or both are columns replace the reference with the following:
    SUM('yourtablename'[score])

    You may have to enclose the above into a CALCULATE(...)

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

      TomMartens

       

      I am not asking about comparison . I asking how to find previous record column to do comparison

      • TomMartens's avatar
        TomMartens
        Super User
        8 years ago

        Hey,

         

        can you please provide some sample data, that we can easily copy to Power BI and also provide how var1 to varn is sorted, are there possiblities that there are var1 and var3 in a group and for this group var1 provides the previous data for var3?

         

        Regards

        Tom