Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I've been scouring the forum, but haven't found a solution that works for me. I am trying to calculate the net change of a value from the current row to the previous row. Normally, I would consider creating a prior month column, but in my data I can have several rows within the same month.
Sample Data
Project Number ForecastDate Official Amount RowOverRow Change
1234 2017-12-15 1 $500 $ 0
1234 2017-12-21 1 $600 $100
1234 2018-01-05 0 $650 $ 50
1234 2018-02-10 1 $1,000 $350
The total change is $500. I've tried using the "EARLIER" function and the LOOKUPVALUE function, but I cannot get that to work. I've also gone back to edit queries and added an index column.
Thanks!
Solved! Go to Solution.
Hi @tlenzmeier
Try this calculated Column
Row Over Row Change = VAR previousdate = CALCULATE ( MAX ( TableName[ForecastDate] ), FILTER ( ALLEXCEPT ( TableName, TableName[Project Number] ), TableName[ForecastDate] < EARLIER ( TableName[ForecastDate] ) ) ) VAR PreviousAmount = CALCULATE ( SUM ( TableName[Amount] ), FILTER ( ALLEXCEPT ( TableName, TableName[Project Number] ), TableName[ForecastDate] = previousdate ) ) RETURN IF ( NOT ( ISBLANK ( previousdate ) ), TableName[Amount] - PreviousAmount )
Excellent. Thank you.
User | Count |
---|---|
87 | |
72 | |
68 | |
64 | |
54 |
User | Count |
---|---|
98 | |
89 | |
74 | |
67 | |
62 |