Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PBE
Helper II
Helper II

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 AColumn BColumn C  
RowIndexDateAmountAvailableExcessCalculationExplanation
11721/06/2023122160-2148= 12 - 2160Row1 (Column A - Column B)
21616/06/2023852160-4223= -2148 + 85 - 2160Row1 (column C) + Row 2 (Column A) - Row 2 (Column B)
31514/06/202316602160-4723= -4223 + 1660 - 2160Row 2 (column C) + Row 3 (Column A) - Row 3 (Column B)
41413/06/2023114.82160-6768.2= -4723 + 114.8 - 2160Row 3 (column C) + Row 4 (Column A) - Row 4 (Column B)
51309/06/202310112160-7917.2etcetc
61208/06/20231202160-9957.2  
71106/06/20231119.52160-10997.7  
81002/06/20231234.31920-11683.4  
2 ACCEPTED SOLUTIONS
v-jianboli-msft
Community Support
Community Support

Hi @PBE ,

 

Please try:

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

Final output:

vjianbolimsft_0-1684894493080.png

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.

View solution in original post

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:

vjianbolimsft_0-1685064697294.png

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.

View solution in original post

7 REPLIES 7
v-jianboli-msft
Community Support
Community Support

Hi @PBE ,

 

Please try:

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

Final output:

vjianbolimsft_0-1684894493080.png

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

PBE_0-1685028367258.png

 

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.

PBE_1-1685028585381.png

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:

vjianbolimsft_0-1685064697294.png

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 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!

PBE_0-1685133294602.png

Thanks

 

Pete

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.

Thanks for your help

Hi Jianbo Li

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

Kind Regards

 

Pete

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.