Using DAX to calculate difference on previous row

[Anonymous]

Hi All,

I have the following table and would like to translate progress on a project, to the actual percentage contributed per month. So essentially I would like to do a difference between row 3 and row 2 of the "Project A" column. I have been trying other solutions online using DAX but without success. The table below illustrates "Project A - Progress per month" which I wish to calculate with DAX.

Table1

Month:      Project A       Project A - Progress per month

Jul-20        0%                 (blank) 

Aug-20      8%                 8%

Sep-20       18%              10%

Oct-20       27%               9%

Nov-20      35%               8%

Dec-20      47%               12%

[vikrantarora25 · Accepted solution]

First add an Index column to your table using query editor, then add the calculated column for % contribution & use Lookupvalue function with IF. 

Table1[% Contribution] =
IF (
    Table1[Index] = 1,
    LOOKUPVALUE ( Table1[Project A], Table1[Index], Table1[Index] ), 
    LOOKUPVALUE ( Table1[Project A], Table1[Index], Table1[Index] )
        - LOOKUPVALUE ( Table1[Project A], Table1[Index], Table1[Index] - 1 )
)

If this solves your query please mark this as a accepted solution.

Regards

[Anonymous]

Thank you, worked perfectly and gave the exact output I required.

Thanks!