Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Difference between 2 rows please?

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 DateValueExcel Formula
101/01/2019            500 
101/02/2019            800300
201/01/2019            200 
201/02/2019            400200
201/03/2019            900500
201/04/2019         1,700800
301/01/2019            600 
401/01/2019            800 
401/02/2019         1,000200
501/01/2019               50 
501/02/2019               8030
Need Help
Tutorial Requests

1 Reply

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    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  Datanaut