Compare 2 rows

[Anonymous]

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, if row 1 from code is different from row 2 from code, and QtdDateInv is null I get "True".

Scenario 1 - I want to get "False":

Customer          DateTime          Code          QtdDateInv
C00001          04-02-2017         5245             
C00001          02-01-2017         5245             15-01-2017
C00001          25-11-2016         5240             30-11-2016
C00001          12-05-2016         987798           15-05-2016

Scenario 2 - I want to get "True":

Customer          DateTime          Code          QtdDateInv
C00031          04-02-2017         6200             
C00031          02-01-2017         5245             15-01-2017
C00031          25-11-2016         5240             30-11-2016
C00031          26-01-2016         45455240         31-01-2016

I think that I need to get a mease/calculated column to get the last two Datetime and compare the code. I accept suggestions.

Ty.

[Anonymous · Accepted solution]

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]

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]

@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]

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

(...)