Calculate Difference between 2 rows based on multiple column filters BUT handle the duplicate values

[eyewee]

I have the following table called filtered_POR050:

Here is the sample: link 

I would want to find the difference between hours (HEURE LIV) for each ID TOURNEE, filtered by date(JOUR LIV) and postal code (GroupeCP). If the previous value is absent, then the result should be 0.
Currently this is how the final result may look like.

The following solution:

DETLA = 
VAR _earlier = CALCULATE(MAX(filtered_POR050[Label_HEURE LIV]), FILTER(filtered_POR050, filtered_POR050[ID TOURNEE] = EARLIER(filtered_POR050[ID TOURNEE]) && filtered_POR050[JOUR LIV] = EARLIER(filtered_POR050[JOUR LIV]) && filtered_POR050[CP] = EARLIER(filtered_POR050[CP]) && filtered_POR050[Label_HEURE LIV] < EARLIER(filtered_POR050[Label_HEURE LIV])))
RETURN
IF(_earlier <> BLANK(), filtered_POR050[Label_HEURE LIV] - _earlier, 0)

only works partially as it gives wrong return values when dealing with duplicate lines. As shown here:

The difference between these lines should be 0 instead of what is seen.

Any ideas?

[ryan_mayu · Accepted solution]

@eyewee 

one way is to remove duplicates in PQ

the other way is to create an index column in pq

then modify the column

DETLA = 
VAR _earlier = CALCULATE(MAX(Sheet1[Index]), FILTER(Sheet1, Sheet1[ID TOURNEE] = EARLIER(Sheet1[ID TOURNEE]) && Sheet1[JOUR LIV] = EARLIER(Sheet1[JOUR LIV]) && Sheet1[CP] = EARLIER(Sheet1[CP]) && Sheet1[Label_HEURE LIV] <= EARLIER(Sheet1[Label_HEURE LIV])&&'Sheet1'[Index]<EARLIER('Sheet1'[Index])))
VAR _earlier2=maxx(FILTER(Sheet1,'Sheet1'[Index]=_earlier),Sheet1[Label_HEURE LIV])
RETURN
IF(_earlier2 <> BLANK(), Sheet1[Label_HEURE LIV] - _earlier2, 0)

pls see the attachment below

[Anonymous · Accepted solution]

Hi @eyewee ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Measure =
VAR _1 =
    CALCULATE (
        MAX ( Sheet1[Index] ),
        FILTER (
            ALL ( Sheet1 ),
            Sheet1[JOUR LIV] = SELECTEDVALUE ( Sheet1[JOUR LIV] )
                && Sheet1[CP] = SELECTEDVALUE ( Sheet1[CP] )
                && Sheet1[Index] < SELECTEDVALUE ( Sheet1[Index] )
        )
    )
VAR _2 =
    CALCULATE (
        MAX ( Sheet1[HEURE LIV] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[Index] = _1 )
    )
VAR _3 =
    RANKX (
        FILTER (
            ALL ( Sheet1 ),
            Sheet1[JOUR LIV] = SELECTEDVALUE ( Sheet1[JOUR LIV] )
                && Sheet1[CP] = SELECTEDVALUE ( Sheet1[CP] )
        ),
        CALCULATE ( MAX ( Sheet1[Index] ) ),
        ,
        ASC,
        DENSE
    )
VAR _4 =
    MAX ( Sheet1[HEURE LIV] ) - _2
RETURN
    IF ( _3 = 1, TIME ( 0, 0, 0 ), _4 )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

@eyewee 

one way is to remove duplicates in PQ

the other way is to create an index column in pq

then modify the column

DETLA = 
VAR _earlier = CALCULATE(MAX(Sheet1[Index]), FILTER(Sheet1, Sheet1[ID TOURNEE] = EARLIER(Sheet1[ID TOURNEE]) && Sheet1[JOUR LIV] = EARLIER(Sheet1[JOUR LIV]) && Sheet1[CP] = EARLIER(Sheet1[CP]) && Sheet1[Label_HEURE LIV] <= EARLIER(Sheet1[Label_HEURE LIV])&&'Sheet1'[Index]<EARLIER('Sheet1'[Index])))
VAR _earlier2=maxx(FILTER(Sheet1,'Sheet1'[Index]=_earlier),Sheet1[Label_HEURE LIV])
RETURN
IF(_earlier2 <> BLANK(), Sheet1[Label_HEURE LIV] - _earlier2, 0)

pls see the attachment below