cancel
Showing results for
Did you mean:
Frequent Visitor

## 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

 Order Tasknumber Department Creation date 16928134 mv01 mv 18-4-2019 16928134 mv02 mv 18-4-2019 16928134 mv03 mv 15-5-2019 16928134 mv04 mv 15-5-2019 16928139 mv01 mv 20-1-2020 16928139 mv02 mv 20-1-2020 16928139 mv03 mv 13-2-2020 16928139 mv04 mv 19-2-2020 16928139 mv05 mv 19-2-2020 16928139 mv06 mv 13-3-2020 16928139 mv07 mv 18-3-2020 16928139 mv08 mv 18-3-2020 16928139 mv09 mv 23-3-2020 16928142 mv01 mv 6-5-2019 16928142 mv02 mv 6-5-2019 16928142 mv03 mv 8-11-2020 16928142 mv04 mv 20-11-2020 16928142 mv05 mv 20-11-2020 16928149 mv01 mv 21-1-2020 16928149 mv02 mv 21-1-2020 16928149 mv03 mv 6-7-2020 16928149 mv04 mv 6-7-2020 16928149 mv05 mv 6-7-2020 16928154 mv01 mv 22-5-2020 16928154 mv02 mv 5-8-2020 16928154 mv03 mv 25-9-2020 16928154 mv04 mv 25-9-2020 16928154 mv06 mv 23-11-2020 16928154 mv07 mv 23-11-2020 16928156 mv01 mv 2-7-2020 16928156 mv02 mv 2-7-2020 16928156 mv03 mv 2-7-2020 16928158 mv01 mv 3-12-2020 16928158 mv02 mv 3-12-2020 16928158 mv03 mv 16-1-2021 16928158 mv04 mv 23-1-2021 16928158 mv05 mv 23-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".

 Order Tasknumber Department Creation date Difference 16928134 mv01 mv 18-4-2019 0 16928134 mv02 mv 18-4-2019 0 16928134 mv03 mv 15-5-2019 27 16928134 mv04 mv 15-5-2019 27 16928139 mv01 mv 20-1-2020 0 16928139 mv02 mv 20-1-2020 0 16928139 mv03 mv 13-2-2020 24 16928139 mv04 mv 19-2-2020 30 16928139 mv05 mv 19-2-2020 30 16928139 mv06 mv 13-3-2020 53 16928139 mv07 mv 18-3-2020 58 16928139 mv08 mv 18-3-2020 58 16928139 mv09 mv 23-3-2020 63 16928142 mv01 mv 6-5-2019 0 16928142 mv02 mv 6-5-2019 0 16928142 mv03 mv 8-11-2020 552 16928142 mv04 mv 20-11-2020 564 16928142 mv05 mv 20-11-2020 564 16928149 mv01 mv 21-1-2020 0 16928149 mv02 mv 21-1-2020 0 16928149 mv03 mv 6-7-2020 167 16928149 mv04 mv 6-7-2020 167 16928149 mv05 mv 6-7-2020 167 16928154 mv01 mv 22-5-2020 0 16928154 mv02 mv 5-8-2020 75 16928154 mv03 mv 25-9-2020 126 16928154 mv04 mv 25-9-2020 126 16928154 mv06 mv 23-11-2020 185 16928154 mv07 mv 23-11-2020 185 16928156 mv01 mv 2-7-2020 0 16928156 mv02 mv 2-7-2020 0 16928156 mv03 mv 2-7-2020 0 16928158 mv01 mv 3-12-2020 10 16928158 mv02 mv 3-12-2020 154 16928158 mv03 mv 16-1-2021 198 16928158 mv04 mv 23-1-2021 205 16928158 mv05 mv 23-1-2021 51

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

Regards,

Marc

1 ACCEPTED SOLUTION
Community Support

Hi, @marcvz

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 ) )

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 3
Community Support

Hi, @marcvz

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 ) )

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.

Frequent Visitor

Hi Janey,

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

I accepted it as a solution!

Have a nice day

Super User

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

Proud to be a Super User!

Announcements