Use previous row result as base of calculation

Beginer007 — Thu, 14 Mar 2024 04:58:24 GMT

Hello folks !

I hope you are doing well 😁

I'm developping a tool on PBI to calculate a projected stock, i'm really blocked with the formula & DAX command.

Basicly i use the month stock of my clients to calculate their needs and i do a simulation with my shipping plan.

Here my draft :

Basicly to calculate the projected stock i do :

Projected Stock = [Stock] + [Inbound M-1] - [2024+%]

[Stock] is the declared stock by the client and its blank if we don't have the data

[Inbound M-1] is the entering stock to the client warehouse

[2024+%] is the sell-out of my client with a % of depletion

All the rows are diferents mesures coming from a data base and converted to the same unit.

What I want to do is that if my [Stock] = blank(), I use the previous result of the projected stock as [Stock] to simulate the new projected stock for the next month and do it recursively.

I hope you'll understand my problem and try to help me.

The column "Test2" its my tentative and its working for march but not for the other months, here my code :

Test 2 = if([Stock]=BLANK(), var _step1 = [Projected Stock M-1]+[Cumul inbound M-1] - [Cumul 2024 + %] var _step2 = if([Projected Stock M-1]=BLANK(),_step1+[Cumul Stock]+ [Cumul inbound M-1] - [CA 2024 + DEPLETION],_step1) return _step2,[Projected Stock])

Many thanks to all who will take the time to help a Beginner !

Best regards !

Re: Use previous row result as base of calculation

lbendlin — Thu, 14 Mar 2024 16:54:24 GMT

This is impossible to do in standard DAX as it has no concept of a modifiable aggregator. It can be done in Power Query via List.Accumulate. It should also be possible with the new "Visual Calculations" that run on the visual level and can reference "PREVIOUS" rows or columns.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

topic Re: Use previous row result as base of calculation in DAX Commands and Tips

Use previous row result as base of calculation

Re: Use previous row result as base of calculation