Forum Discussion
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 |
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.
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.
7 Replies
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.
Hi Jianbo Li
Thank you very much this works well. Your help is much appreciated.
Kind Regards
Pete
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
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.
