cancel
Showing results for
Did you mean:
Helper I

## Use Previous Value In Column In Calculation

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
2 ACCEPTED SOLUTIONS
Community Support

Hi @PBE ,

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

Community Support

Hi @PBE ,

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

7 REPLIES 7
Community Support

Hi @PBE ,

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

Helper I

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

Community Support

Hi @PBE ,

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

Helper I

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

Community Support

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.

Helper I

Thanks for your help

Helper I

Hi Jianbo Li

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

Kind Regards

Pete