Trouble reproducting an Excel function that uses the reseults from previous rows

[Rob_Jackson]

Hi

Can anybody help me reproduce the following excel formula in a dax formula.  I can get the cumulative column working but cannot  retrieve the previous row data for the labour scrap calculation.

Regards

Rob

[v-eachen-msft]

Hi @Rob_Jackson ,

Here is my sample data.

At first, you need to add an index column in the query editor.
Then you need to create a column like this.

Cost(Scrap Parts) =
VAR a =
    CALCULATE (
        SUM ( 'Table'[Cost] ),
        FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) )
    )
RETURN
    'Table'[Scrap Rate] * a

Now you can get the result.

Best Regards,

Eads

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rob_Jackson]

Hi

Thanks for offering a solution.  I think you are nearly there!  I recreated your table in excel and got slightly different results.  I don't think I was clear in the excel formula.  It needs to be incremental in summing all the previous Costs(Scrap Parts) Column.

I hope the table below with the excel formula is clearer.

Scrap Rate	Cost	Cost (Scrap Parts)		FORMULA
0.02	10	0.20		=B3*C3
0.03	20	0.91		=B4*SUM(D3,C3:C4)
0.04	15	1.84		=B5*SUM(D3:D4,C3:C5)
0.05	5	2.65		=B6*SUM(D3:D5,C3:C6)
0.1	35	9.06		=B7*SUM(D3:D6,C3:C7)

Regards

Rob