Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Compare 2 rows

Hi guys,   I've two rows that I want to compare:   Scenario 1:   In this scenario I want: - According to the two DateTime most recent, If row 1 from Code = row 2 from Code, I get "False", but,...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Anonymous,

     

    You can try to use below measure if it suitable for your requirement.

     

     

    Check = 
var currDate=MAX([DateTime])
var currCustomer=LASTNONBLANK(Test1[Customer],[Customer])
var currCode=LOOKUPVALUE(Test1[Code],Test1[DateTime],currDate,Test1[Customer],currCustomer)
var currQtd=LOOKUPVALUE(Test1[QtdDateInv],Test1[DateTime],currDate,Test1[Customer],currCustomer)
var prevCode=LOOKUPVALUE(Test1[Code],Test1[DateTime],MAXX(FILTER(ALL(Test1),[DateTime]<currDate),[DateTime]),Test1[Customer],currCustomer)
return
if(currCode<>prevCode&&currQtd=BLANK(),TRUE(),FALSE())

     

    Logic : use date and customer to find out the specific records, then compare with them.

     

    Regards,

    Xiaoxin Sheng

Datatouille's avatar
Datatouille
Solution Sage
9 years ago

Hi Anonymous

 

The concept of 'previous row' is not so easy to manage in Powerpivot. You need some tricks with Filter, earlier, etc.

 

I guess the best approach here is to use Power Query with indexes and self-merge.

You can apply the technique MattAllington shows in this article.