Find previous row using index on a filtered table

[jefflynn]

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

[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.

[jefflynn]

Thank you. The numbers are off:

[ArwaAldoud]

@jefflynn find attcheded file

https://drive.google.com/file/d/1WmATvtQDjBby-nqQON_Bpf-RqkW9IXgi/view?usp=sharing 

[ArwaAldoud · Accepted solution]

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