Calculate on row below current row

[vrossouw]

https://drive.google.com/drive/folders/1Vd3I930HLd8xfV5nnZH_SvI7BzMHXSfv?usp=sharing

Hi, I am trying to base a calculation on the row below the current line.

I have saved the excel file in Google Drive - please see the link.

Split Route	Scrap	Gross	Gross2	Grossed up	100%
010770AGAE	0.15	1.102	1.00	110.21	100.00
010770AGAE	1.8	1.100	0.98	110.05	-
010770AGAE	0.5	1.081	1.00	108.07	-
010770AGAE	7	1.075	0.93	107.53	-
010780AGBE	0.15	-	1.00	-	100.00

Column E (Grossed Up) has got a formula in Excel which looks at the row below the current row.

How can I get the same result in Power BI?

Thank you for your time.

Regards

Vicky

[TomMartens · Accepted solution]

Hey, basically, you can't, because there is no concept of ordered rows. A table in Power BI or SSAS Tabular has to be considered a heap, an unordered number of rows. This is similar to a table (w/o an index) in a relational database. For this reason, you have to provide an index, e.g. using Power Query, after you have sucessfully ordered the rows. Then you can use the DAX function LOOKUPVALUE(''[columntoreturn], ''[indexcolumn],''[indexcolumn]+1) to access the rows below, assuming the rows have been numbered in ascending order, otherwise it would be -1 Regards, Tom