Forum Discussion

eyewee's avatar
eyewee
Frequent Visitor
2 years ago

Calculate Difference between 2 rows based on multiple column filters

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?

Data Protection
Need Help
Tutorial Requests

4 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    eyewee 

    what's the sequence of the data?

     

    what if the time is earlier than the previous row?

     

    • eyewee's avatar
      eyewee
      Frequent Visitor
      2 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      eyewee
      Frequent Visitor
      2 years ago

      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?