Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate difference between two rows (not previous row)

Hi All, 

 

This is my first post here. I have been looking for a solution to my problem for a while now. 

But couldn't found it yet. So here's is my problem

I have a dataset with orders and tasks on the order, see below

 

OrderTasknumberDepartmentCreation date
16928134mv01mv18-4-2019
16928134mv02mv18-4-2019
16928134mv03mv15-5-2019
16928134mv04mv15-5-2019
16928139mv01mv20-1-2020
16928139mv02mv20-1-2020
16928139mv03mv13-2-2020
16928139mv04mv19-2-2020
16928139mv05mv19-2-2020
16928139mv06mv13-3-2020
16928139mv07mv18-3-2020
16928139mv08mv18-3-2020
16928139mv09mv23-3-2020
16928142mv01mv6-5-2019
16928142mv02mv6-5-2019
16928142mv03mv8-11-2020
16928142mv04mv20-11-2020
16928142mv05mv20-11-2020
16928149mv01mv21-1-2020
16928149mv02mv21-1-2020
16928149mv03mv6-7-2020
16928149mv04mv6-7-2020
16928149mv05mv6-7-2020
16928154mv01mv22-5-2020
16928154mv02mv5-8-2020
16928154mv03mv25-9-2020
16928154mv04mv25-9-2020
16928154mv06mv23-11-2020
16928154mv07mv23-11-2020
16928156mv01mv2-7-2020
16928156mv02mv2-7-2020
16928156mv03mv2-7-2020
16928158mv01mv3-12-2020
16928158mv02mv3-12-2020
16928158mv03mv16-1-2021
16928158mv04mv23-1-2021
16928158mv05mv23-1-2021

 

I want to have the difference per order from the first mv (department) task. ( I have even more departments, but if someone has the answer for this one already it would really help!)

 

See below example, for what I am looking for, column "Difference".

 

 

OrderTasknumberDepartmentCreation dateDifference
16928134mv01mv18-4-20190
16928134mv02mv18-4-20190
16928134mv03mv15-5-201927
16928134mv04mv15-5-201927
16928139mv01mv20-1-20200
16928139mv02mv20-1-20200
16928139mv03mv13-2-202024
16928139mv04mv19-2-202030
16928139mv05mv19-2-202030
16928139mv06mv13-3-202053
16928139mv07mv18-3-202058
16928139mv08mv18-3-202058
16928139mv09mv23-3-202063
16928142mv01mv6-5-20190
16928142mv02mv6-5-20190
16928142mv03mv8-11-2020552
16928142mv04mv20-11-2020564
16928142mv05mv20-11-2020564
16928149mv01mv21-1-20200
16928149mv02mv21-1-20200
16928149mv03mv6-7-2020167
16928149mv04mv6-7-2020167
16928149mv05mv6-7-2020167
16928154mv01mv22-5-20200
16928154mv02mv5-8-202075
16928154mv03mv25-9-2020126
16928154mv04mv25-9-2020126
16928154mv06mv23-11-2020185
16928154mv07mv23-11-2020185
16928156mv01mv2-7-20200
16928156mv02mv2-7-20200
16928156mv03mv2-7-20200
16928158mv01mv3-12-202010
16928158mv02mv3-12-2020154
16928158mv03mv16-1-2021198
16928158mv04mv23-1-2021205
16928158mv05mv23-1-202151

 

Hope you understand my question and hopefully someone can help me. 

 

Regards,

Marc

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, Anonymous 

     

    According to your description, you can create a measure to calculate the desired result.

    Like this:

    Measure =
VAR a =
    MINX (
        FILTER (
            ALL ( 'Table' ),
            [Department] = SELECTEDVALUE ( 'Table'[Department] )
                && [Order] = SELECTEDVALUE ( 'Table'[Order] )
        ),
        [Creation date]
    )
RETURN
     ( DATEDIFF ( a, SELECTEDVALUE ( 'Table'[Creation date] ), DAY ) )

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

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

3 Replies

  • negi007's avatar
    negi007
    Community Champion
    5 years ago

    Anonymous Hi Mark, what is the formual to calculate the difference value. Can you please share it so that we can provide you the solution. Thanks

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi, Anonymous 

     

    According to your description, you can create a measure to calculate the desired result.

    Like this:

    Measure =
VAR a =
    MINX (
        FILTER (
            ALL ( 'Table' ),
            [Department] = SELECTEDVALUE ( 'Table'[Department] )
                && [Order] = SELECTEDVALUE ( 'Table'[Order] )
        ),
        [Creation date]
    )
RETURN
     ( DATEDIFF ( a, SELECTEDVALUE ( 'Table'[Creation date] ), DAY ) )

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Janey, 

       

      Thank you so much. This was exactly what I was looking for!

       

      I accepted it as a solution!

       

      Have a nice day