Difference between two rows with previous day, same Work Item Id

[Anonymous]

Completed Daily = for a given row, it is the difference between the Completed Work column and the previous value (previous day, same Work Item Id)

http://prntscr.com/ktxpn4

Example: 

For Work ID: 85643

I need result:

First Row: 0

Scond Row: 2.5

Third Row: 4.5-2.5 = 2

Forth Row 9.00-4.5= 4.50

Need to subtracted above row value with group by of column (Work ID and Date) 

Reult look like this:

[Zubair_Muhammad]

@Anonymous

Try this calculated column

Completed Daily =
[work]
    - MINX (
        TOPN (
            1,
            FILTER (
                table1,
                [Work Item Id] = EARLIER ( [Work item Id] )
                    && [Changed date] > EARLIER ( [Changed date] )
            ),
            [Changed Date], ASC
        ),
        [Work]
    )

[Anonymous]

@Zubair_Muhammad

Thanks This logic helpful. 

But Stil have one problem. It show result in Wrong column. 

it show result in wrog rows.

First Row show have result 0 and second row result is -2.5 like this 
0

2

2.5

4.5

0

[Anonymous · Accepted solution]

Hi @Anonymous,

You can try to use below calculate column formula to achieve your requirement:

Completed Daily =
VAR previous =
    CALCULATE (
        MAX ( Test[Changed Date] ),
        FILTER (
            ALL ( Test ),
            [Work Item Id] = EARLIER ( [Work Item Id] )
                && [Changed Date] < EARLIER ( Test[Changed Date] )
        )
    )
RETURN
    [Work]
        - LOOKUPVALUE (
            Test[Work],
            Test[Work Item Id], [Work Item Id],
            Test[Changed Date], previous
        )

Regards.

Xiaoxin Sheng

[Anonymous]

Hi 

May I know the formula in Power BI to calculate the [Gap Days] between two dates in a row for specific item?

Currently I want to calculate difference between two rows based on specific item. This is my data:

Item	Date	Gap Days
A	1-Jan-20
A	7-Jan-20	6
A	10-Jan-20	3
B	2-Jan-20
B	5-Jan-20	3
C	8-Jan-20
C	16-Jan-20	8
C	25-Jan-20	9
D	15-Jan-20
D	31-Jan-20	16

The gap days is the dates in the row minus the date in the previous row for the same item. 

Really appreciate your help.

Thanks

Regards,

Caroline

[Anonymous]

Thank you so much for help