Column with value from previous day / row

[Anonymous]

Hey there

I have a table for Add, Minus, Net Mvmt and Closing bal by date setup as columns in PowerBI.

How can I add a column (Opening Bal) which takes the Closing Balance from previous date?

Thanks!

	Add	Minus	Net Mvmt	Closing Bal	Opening Bal
6-Jan	100	50	50	50
7-Jan	200		200	250	50
8-Jan	300	100	200	450	250

[v-shex-msft]

HI @Anonymous,

If your table not contain duplicate date, you can direct use data as iterator to looping your records.

Opening Bal =
VAR prevDate =
    CALCULATE (
        MAX ( Table[Date] ),
        FILTER ( ALL ( Table ), [Date] < EARLIER ( Table[Date] ) )
    )
RETURN
    LOOKUPVALUE ( Table[Closing Bal], Table[Date], prevDate )

If not, you can consider to use PattemManohar's suggestion to add index column as iterator.

Regards,
Xiaoxin Sheng

[PattemManohar · Accepted solution]

@Anonymous Please add and Index column in Power Query Editor and then use the below DAX as New Column.

If your data is not in sorted order by date then add a Rank column as below instead of Index and then use this Rank field in place of Index in DAX formula

Rnk = RANKX(Test169PrevRow,Test169PrevRow[Date],,ASC)

OpeningBal = LOOKUPVALUE(Test169PrevRow[Closing Bal],Test169PrevRow[Index],Test169PrevRow[Index]-1)