Use Previous Value In Column In Calculation

[PBE]

Hi, I don't know how to do this in Power BI.

Any ideas on how to do this in DAX or Power Query.

I found this not easy to explain so any questions please let me know.

Thanks

Pete

The first five columns are a query from database.

The Excess column is calculated as shown.

For row 1:- Amount less Available

For row 2 onwards:- Excess figure from previous row + Amount from current row - Available from current row

			Column A	Column B	Column C
Row	Index	Date	Amount	Available	Excess	Calculation	Explanation
1	17	21/06/2023	12	2160	-2148	= 12 - 2160	Row1 (Column A - Column B)
2	16	16/06/2023	85	2160	-4223	= -2148 + 85 - 2160	Row1 (column C) + Row 2 (Column A) - Row 2 (Column B)
3	15	14/06/2023	1660	2160	-4723	= -4223 + 1660 - 2160	Row 2 (column C) + Row 3 (Column A) - Row 3 (Column B)
4	14	13/06/2023	114.8	2160	-6768.2	= -4723 + 114.8 - 2160	Row 3 (column C) + Row 4 (Column A) - Row 4 (Column B)
5	13	09/06/2023	1011	2160	-7917.2	etc	etc
6	12	08/06/2023	120	2160	-9957.2
7	11	06/06/2023	1119.5	2160	-10997.7
8	10	02/06/2023	1234.3	1920	-11683.4

[v-jianboli-msft · Accepted solution]

Hi @PBE ,

Please try:

Excess = SUMX(FILTER('Table',[Row]<=EARLIER('Table'[Row])),[Amount]-[Available])

Final output:

Best Regards,

Jianbo Li

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

[v-jianboli-msft · Accepted solution]

Hi @PBE ,

Please try:

Excess = SUMX(FILTER('Table',[Row]<=EARLIER('Table'[Row])),[Amount]-[Available])

Final output:

Best Regards,

Jianbo Li

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

[PBE]

Hi Jianbo Li

Thank you for your help before. I wonder if you would be able to help me a bit further on this.

The above works great for a static list. However in my actual data I have a Work Type column and use a slicer to select the relevant one.

If I don't select one the Excess column calculates from row 1 onwards.

However if I select one it does not calculate properly as it does not start from first selected row. In this case it needs to start from row 108 and not overall row 1.

Is it possible to create a dynamic row column which starts at 1 no matter which work type is selected. Hopefully this would make your DAX formula work for different selections?

Thanks

Pete

[v-jianboli-msft · Accepted solution]

Hi @PBE ,

Please try:

Measure = 
var _a = ALLSELECTED('Table'[Work Type])
var _b = MAX('Table'[Row])
return SUMX(FILTER(ALL('Table'),[Work Type] in _a&&[Row]<=_b),'Table'[Amount]-'Table'[Available])

Output:

Best Regards,

Jianbo Li

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

[PBE]

Hi Jianbo Li

Thank you very much it works well.

I wonder if you could help with one last thing.

I need an extra column which highlights where Amount - Available is a positive number. Please see explanation below. I hope it makes sense!

Thanks

Pete

[v-jianboli-msft]

Hi @PBE ,

Sorry, this question is beyond the topic at the beginning of the post.

In order to make the post more relevant, please consider posting a new thread for this issue so that more users can participate and also better help other users with similar problems.

Thank you for your understanding!🙂

Best Regards,

Jianbo Li

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

[PBE]

Thanks for your help

[PBE]

Hi Jianbo Li

Thank you very much this works well. Your help is much appreciated.

Kind Regards

Pete