cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chotu27
Post Patron
Post Patron

Get Previous record

Hi All,

 

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

 

Any Help with DAX please help me 

 

Previous Score.PNG

14 REPLIES 14
TomMartens
Super User
Super User

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:

image.png

 

Hopefully this is what you are looking for.

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens

 

Yes, But your calculation is not working for me

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


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi,

Please check sample data here  .  

 

Columns need for calculation check below image

 

Previous Score.PNG

 

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:

image.png

 

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:

 

image.png

 

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.

 

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens

 

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

 

error.PNG

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)

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
    )
)


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

 

error.PNG

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
    )

Get Previous record.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @chotu27,

 

Is my solution helpful?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
TomMartens
Super User
Super User

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


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens

 

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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.