cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marcvz
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

 

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

1 ACCEPTED SOLUTION
v-janeyg-msft
Community Support
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 ) )

9.png

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.

View solution in original post

3 REPLIES 3
v-janeyg-msft
Community Support
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 ) )

9.png

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.

View solution in original post

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
Super User
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




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors