Forum Discussion
Using DAX to calculate difference on previous row
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%
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
2 Replies
vikrantarora25Helper I
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