Self referencing calculated column (referencing previous row) - Power M help

[Federaske]

Good evening,

hope you guys can help me! I inherited an old heavy-hard coded file with various cell references and formulas I am now trying to automate.

I need one last step with my PQ query, I need to add a calculated column that is referencing to the previous row of itself to complete a calculation. @ImkeF, I am mentioning you since I have seen a post which you already solved with a very similar issue (https://community.powerbi.com/t5/Desktop/Calculated-column-with-previous-row-referencing-same-calcul...)

Here below an example of what I need to calculate --> AVAILABLE is the calculated column I need (see on the right the calculation)

PRODUCT ID	ON-ORDER	ON-HAND	AVAILABLE
ABC	31	500	469	(ONHAND[i] - ONORDER[i])
ABC	29	500	440	(AVAILABLE[i-1] - ONORDER[i])
ABC	4	500	436	(AVAILABLE[i-1] - ONORDER[i])
DEF	26	780	751	(ONHAND[i] - ONORDER[i])
DEF	25	780	726	(AVAILABLE[i-1] - ONORDER[i])
DEF	89	780	637	(AVAILABLE[i-1] - ONORDER[i])
DEF	168	780	469	(AVAILABLE[i-1] - ONORDER[i])
GHI	56	-36	-92	(ONHAND[i] - ONORDER[i])
GHI	56	-36	-148	(AVAILABLE[i-1] - ONORDER[i])
LMN	348	220	-128	.....
LMN	26	220	-154	.....
LMN	125	220	-279	.....

Thank you already for the help.

Federico

[V-lianl-msft · Accepted solution]

Hi @Federaske ,

Please add an index column in the query editor, and then use DAX to create the following new column:

Column = 
var on_order= CALCULATE(SUM('Table'[ON-ORDER]),FILTER(ALLEXCEPT('Table','Table'[PRODUCT ID]),EARLIER('Table'[Index])>='Table'[Index]))
return 'Table'[ON-HAND]-on_order

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

[Federaske]

Hi Liang,

thank you very much! Works perfectly.

Thanks again,

Bye,

Federico

[lbendlin]

You must have an index column somewhere. Please include it in your sample data.