Calculate Difference between 2 rows based on multiple column filters

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


Please, can someone help me with it?

[Anonymous]

Hi @eyewee 

You can create a calculated column as follows.

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)

Is this the result you expect?

Best Regards,
Community Support Team _Yuliax

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

[eyewee]

Than you for your reply, your sotion does appear correct at first glance, but when I look into the Table View, I see the following:

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

Also, could you please explain how does your dax expression work? How would I do if I wanted to display only the values that are equal or greater than 15 min (>=15) ? Or if I wanted to get a sum of these conditionned values per ID TOURNEE?

[ryan_mayu]

@eyewee 

what's the sequence of the data?

what if the time is earlier than the previous row?

[eyewee]

Thank you for your question, the data is presented in a strictly chronological order, thus, the two lines you've pointed on, do not belong to the same ID TOURNEE, when data is isolated by ID TOURNEE, CPgroupe and JOUR LIV, there will only be hours ranged in ascending order.