Get Previous record

[chotu27]

Hi All,

I wanted to find  previous record in matrix visual based on hierachy displayed below.

Any Help with DAX please help me 

[TomMartens]

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]

@TomMartens

Yes, But your calculation is not working for me

[TomMartens]

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]

Hi,

Please check sample data here  .  

Columns need for calculation check below image

[TomMartens]

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.

[chotu27]

@TomMartens

Hi Yes that is what i wanted but im getting below error in calculation 

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

[TomMartens]

The error indicates, that LOOKUPVALUE would retreive more than one value, this is not possible, so for this reason an Aggregate function has to been applied:

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

[chotu27]

@TomMartens Got it Worked but still it is not showing correct previous values please check attached  Pbix

[v-jiascu-msft]

Hi @chotu27,

Maybe there is a solution you can give it a try. Please check it out in the attachment.

1. In the Query Editor, sort the "SubjectNumber" first, then sort the column "VisitName". 

2. Add an index to keep the order we make in step 1.

3. Add a custom column.

if [Index.1] = 0 then
    [Index.1] else if 
    [VisitName] = #"Added Index"{[Index.1] - 1}[VisitName] then 
    null else 
    [Index.1]

4. Right-click the column "Custom", choose Fill -> Down.

5. Apply the changes, and create a measure.

Measure =
VAR lastIndex =
    CALCULATE (
        MAX ( 'Table'[Custom] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[SubjectNumber] ),
            'Table'[Custom] < MAX ( 'Table'[Custom] )
        )
    )
RETURN
    IF (
        ISBLANK ( lastIndex ) = FALSE ()
            && HASONEVALUE ( 'Table'[VisitName] ),
        CALCULATE (
            SUM ( 'Table'[SiteRaterScore] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Custom] = lastIndex )
        ),
        0
    )

Best Regards,

Dale

[v-jiascu-msft]

Hi @chotu27,

Is my solution helpful?

Best Regards,

Dale

[TomMartens]

Hey,

it's quite difficult to provide another solution. My example works with the data you provided, please explain what you expect, and show all the data.

You have to be aware that data will be aggregated,, this is the reason why I choose the aggregate function AVERAGE, but maybe you have to choose SUM.

Regards

Tom

[TomMartens]

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]

@TomMartens

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

[TomMartens]

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