Forum Discussion
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, 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.
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
4 Replies
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.
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.
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
(...)
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
