Calculate difference between two rows (not previous row)

[Anonymous]

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

[v-janeyg-msft · Accepted solution]

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]

Hi Janey, 

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

I accepted it as a solution!

Have a nice day

[negi007]

@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