Forum Discussion
Difference between two rows
Hi SSS
Using DAX you can add this calculated column to get desired results
= VAR NextIndex = Table1[Index] + 1 RETURN Table1[Orders] - CALCULATE ( VALUES ( Table1[Orders] ), FILTER ( ALL ( Table1 ), Table1[Index] = NextIndex ) )
Hey guys,
I encountered one problem I simply can't solve. If anyone can help me out I would be extra grateful!
I want to calculate the difference between two rows, but after I import data to the data model, the sort order is mixed up (see below: Report Date and Account Name) so one of my formulas that could work if the sort order is correct doesn't work.
The table looks like this and the Change column like this is my goal.
| Account Name | MRR | Report Date | Change |
| a | 100 | 1/31/2017 0:00 | 100 |
| a | 100 | 2/28/2017 0:00 | 0 |
| a | 150 | 4/30/2017 0:00 | -30 |
| a | 100 | 5/31/2017 0:00 | -50 |
| b | 13 | 7/31/2019 0:00 | 13 |
| b | 13 | 8/31/2019 0:00 | 0 |
| c | 5 | 7/31/2019 0:00 | 5 |
| a | 100 | 6/30/2017 0:00 | 0 |
| a | 100 | 7/31/2017 0:00 | 0 |
| a | 100 | 8/31/2017 0:00 | 0 |
| a | 100 | 9/30/2017 0:00 | 0 |
| a | 100 | 10/31/2017 0:00 | 0 |
| a | 180 | 3/31/2017 0:00 | 80 |
You can notice that the account doesn't have the correct order either by the report date.
If there is any way how to solve it (maybe as a measure? or maybe is there a way how to correctly import data?)
I would be so so grateful.
All the best,
Tomislav
Thank you Ashish_Mathur !
Works amazing!
Thank you so much.
All the best from Croatia!
TomislavYou are welcome.
Hi, I have almost the same data but the column MRR includes blanks, I appreciate your advice to let me know what the formula will look like? i need if the column value is blank to take the earliest value found instead of calculating based on zeros instead of balnks. Thanks.
Hi,
Refer to column1 of the image. You will see that the result in the 5th row is not 100-0=100 but 100-180=-80. You may download my PBI file from here.
Hope this helps.
Hi Ashish, thanks for your support. appreciated.
your solution works fine for the import data but i have direct query data so i cannot add a custom column to do what you adviced, i believe i need the same formula on the power query. i appreciate your support. thanks.
