cancel
Showing results for
Did you mean:
Highlighted
Member

## 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-2016C00001          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-2016C00031          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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Compare 2 rows

Hi @fcarvalho,

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

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

For learning resources/Release notes, please visit: | |
4 REPLIES 4
Established Member

## Re: Compare 2 rows

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.

Member

## Re: Compare 2 rows

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

Member

## Re: Compare 2 rows

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

(...)

Community Support Team

## Re: Compare 2 rows

Hi @fcarvalho,

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

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

For learning resources/Release notes, please visit: | |

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,916)