Excel Formula to DAX: How to Reference Previous Row

Question

I'm trying to create a new column in Power BI Desktop which replicates a basic Excel formula which seems simple enough.

The Excel formula which I am trying to replicate is in column D:

=IF(B2=B1, 0, C2)

So that the returned column should look like column D.

Excel Data Example:

I have the same data structure in Power BI and would like to create a new column to replicate column D in Excel, but I can't find a way to replicate the formula in DAX where it calls for :

B2=B1

Power BI Data Example:

Thanks!

Answer 1

The issue here is that DAX doesn't do relative references between the rows in the same way since there is no inherent ordering of the table. You'll have to use the index column in order to find the appropriate row.

In this case, what you can do it look up the [Cage No] in the row where the [Index Order] is one less than in the current row:

Reported Size = IF(
                    LOOKUPVALUE(
                        Table1[Cage No],
                        Table1[Index Order], Table1[Index Order] - 1
                    ) = Table1[Cage No],
                    0,
                    Table1[Size] )