Forum Discussion
Find previous row using index on a filtered table
I have the following table in Power BI named Query1.
I want to add the CostToComplete column which adds the current EstCostAgg to previous EstCostAgg then subtracts current ActualCostAgg + the previous ActualCostAgg. The current row is found where Description2 contains "SUB LABOR". The previous row will never contain “SUB LABOR”. The Index values are not sequential, there are gaps due to filters on the visual. The index values are sorted in ascending order.
| Phase | Description2 | EstCostAgg | CostToComplete | ActualCostAgg | Index |
| 031006. 1 | DRILLED PIERS - PLACING | 18614 | 17267.43 | 1741 | |
| 031006. 3 | DRILLED PIERS - PLACING (SUB LABOR) | 0 | -10687.93 | 12034.5 | 1747 |
| 031010. 1 | DRILLED PIERS - FORM INSTALL/STRIP | 3597 | 0 | 1781 | |
| 031106. 1 | FOOTINGS - PLACING/FINISHING | 31360 | 4520.37 | 1989 | |
| 031106. 3 | FOOTINGS - PLACING/FINISHING (SUB LABOR) | 0 | 15479.63 | 11360 | 2005 |
| 031108. 1 | FOOTINGS - FORM INSTALL/STRIP | 23846.5 | 3336.2 | 2037 | |
| 031108. 3 | FOOTINGS - FORM (SUB LABOR) | 0 | 19719.5 | 2080 | |
| 031126. 1 | GRADE BEAMS - PLACING/FINISHING | 10599 | 2087.4 | 2258 | |
| 031126. 3 | GRADE BEAMS - PLACING/FINISHING (SUB LABOR) | 0 | 1477.6 | 7034 | 2268 |
| 031130. 1 | GRADE BEAMS - FORM INSTALL/STRIP | 57881 | 14216.21 | 2296 | |
| 031130. 3 | FOOTINGS (GRADE BEAMS) - FORM SIDES (SUB LABOR) | 0 | 5812.79 | 37852 | 2309 |
Hi jefflynn
If I understand correctly, can you achieve this with a calculated column using DAX
CostToComplete_Column =
VAR PreviousRow =
MAXX(
FILTER(Query1, Query1[Index] < EARLIER(Query1[Index])),
Query1[Index]
)VAR PreviousEstCostAgg =
LOOKUPVALUE(Query1[EstCostAgg], Query1[Index], PreviousRow)VAR PreviousActualCostAgg =
LOOKUPVALUE(Query1[ActualCostAgg], Query1[Index], PreviousRow)RETURN
IF(
SEARCH("SUB LABOR", Query1[Description2], 1, 0) > 0,
PreviousEstCostAgg + Query1[EstCostAgg] - (Query1[ActualCostAgg] + PreviousActualCostAgg),
BLANK()
)Find the seccenshot below
If this response was helpful, please accept it as a solution and give kudos to support other community members
4 Replies
- ArwaAldoud
Super User
Hi jefflynn
If I understand correctly, can you achieve this with a calculated column using DAX
CostToComplete_Column =
VAR PreviousRow =
MAXX(
FILTER(Query1, Query1[Index] < EARLIER(Query1[Index])),
Query1[Index]
)VAR PreviousEstCostAgg =
LOOKUPVALUE(Query1[EstCostAgg], Query1[Index], PreviousRow)VAR PreviousActualCostAgg =
LOOKUPVALUE(Query1[ActualCostAgg], Query1[Index], PreviousRow)RETURN
IF(
SEARCH("SUB LABOR", Query1[Description2], 1, 0) > 0,
PreviousEstCostAgg + Query1[EstCostAgg] - (Query1[ActualCostAgg] + PreviousActualCostAgg),
BLANK()
)Find the seccenshot below
If this response was helpful, please accept it as a solution and give kudos to support other community members
Thank you. The numbers are off:
- ArwaAldoud
- Ashish_Mathur
Hi,
Not very clear with what you want. Share the download link of an Excel file with your excel formula written there. I will understand the logic from there and convert those formulas to measures in PowerBI.
