Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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)
Proud to be a PBI Community Champion
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
@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)
Proud to be a PBI Community Champion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |