Difference between 2 rows please?

[Anonymous]

Hi everyone!

My data is structured in Excel (displayed below). I am having translating the Excel formula in Power Bi however, and I need your help please! 

In Excel I sort the data by Key and by Date. In column D I then use this formula : IF(A2=A1,C2-C1,"")

Could you please help me translate this to PowerBi language please? 

Thank you

A	B	C	D
Key	Date	Value	Excel Formula
1	01/01/2019	500
1	01/02/2019	800	300
2	01/01/2019	200
2	01/02/2019	400	200
2	01/03/2019	900	500
2	01/04/2019	1,700	800
3	01/01/2019	600
4	01/01/2019	800
4	01/02/2019	1,000	200
5	01/01/2019	50
5	01/02/2019	80	30

[AlB]

Hi @Anonymous 

I think there are a few posts dealing with this. Create a new calculated column:

NewCol =
VAR PreviousDate_ =
    CALCULATE (
        MAX ( Table1[Date] ),
        Table1[Date] < EARLIER ( Table1[Date] ),
        ALLEXCEPT ( Table1, Table1[Key] )
    )
VAR PreviousValue_ =
    CALCULATE (
        DISTINCT ( Table1[Value] ),
        Table1[Date] < PreviousDate_,
        ALLEXCEPT ( Table1, Table1[Key] )
    )
RETURN
    IF ( NOT ISBLANK ( PreviousValue_ ), Table1[Value] - PreviousValue_ )

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers