Get the Previous Row

Microsoft Excel provides a simple way to refer to previous row.

In Power BI, you can’t use this approach …but 🙂  there are plenty of other techniques to refer to previous row

So for example you can not do this with DAX or in Query Editor

First Method – Use TOPN

If the previous row is based on a previous date or something similar (for example..an Index number)…we can use TOPN function to grab the previous row and then we can use an ITERATOR to grab any specific column from that previous row i.e.

Previous Row =
VAR PR =
 TOPN (
 1,
 FILTER (
 Table1,
 Table1[Products] = EARLIER ( Table1[Products] )
 && Table1[Date] < EARLIER ( Table1[Date] )
 ),
 [Date], DESC
 )
RETURN
 MINX ( PR, [Sales] )

Second Method- Use CALCULATE + ALLEXCEPT

Another way is to grab the previous_date first in a variable and then get the sales for that Date..i.e.

Previous_Row =
VAR Previous_Date =
 CALCULATE (
 MAX ( Table1[Date] ),
 FILTER (
 ALLEXCEPT ( Table1, Table1[Products] ),
 Table1[Date] < EARLIER ( Table1[Date] )
 )
 )
RETURN
 CALCULATE (
 SUM ( Table1[Sales] ),
 FILTER ( ALLEXCEPT ( Table1, Table1[Products] ), Table1[Date] = Previous_Date )
 )

THIRD METHOD

Another Method is to first add an Index Column in your Table using Query Editor…Then we can use a simple DAX formula to grab the previous row value.

Note: Make sure your Table is sorted by date before adding an Index Column

Previous-Row =
CALCULATE (
 SUM ( Table1[Sales] ),
 FILTER (
 ALLEXCEPT ( Table1, Table1[Products] ),
 Table1[Index]
 = EARLIER ( Table1[Index] ) - 1
 )
)

OTHER METHODS

This is why DAX is so beautiful…It provides you so many ways to achieve the same results. There should be other methods as well…Please let me know if you know of another trick(s).

DOWNLOAD

Click here to download the pbix File  and play with it