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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Datatouille, I think the best approch is comparing the two most recent "DateTime" according to the "Customer" column. But I'm stucked getting the correct result.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Searching in power bi community I found this from Vvelarde

       

      I adapted to my tables and used "Earlier", but don't work properly. Can anyone help me?

       

       

      TableWorkSumm = 
SUMMARIZE (
    G_HistRecolha;
    G_HistRecolha[DataRecolha];
    G_HistRecolha[Cliente];
	G_HistRecolha[Leitura];
    "Resultado"; IF (
        CALCULATE( SUM(G_HistRecolha[Leitura] ))
            -  CALCULATE( SUM(G_HistRecolha[Leitura] ); 'G_HistRecolha'[DataRecolha] > EARLIER ( 'G_HistRecolha'[DataRecolha] ))
            =  CALCULATE( SUM( G_HistRecolha[Leitura] ));
        BLANK();
         CALCULATE( SUM(G_HistRecolha[Leitura] ))
            -  CALCULATE( SUM(G_HistRecolha[Leitura] ) ; 'G_HistRecolha'[DataRecolha] > EARLIER ( 'G_HistRecolha'[DataRecolha] ))
    )
)

       

       

       

      and the result expected from the bottom is:

       

      3

      2

      2

      1

      1

      "BLANK"

      1

      (...)