Forecasting values using a previously calculated value

[Joe_Baltimore]

Hello everyone,

I'm trying to forecast for future dates based on previous calculated date's values in Power BI, but I'm having some trouble. 

I have three data tables:

Stock data (value to use, if available)

Inbound Pieces (values to add)

Shipped Pieces (values to subtract)

What I want to do is forecast the value for future dates in the Stock Pieces column using a formula that takes into account the previous calculated date's values. For example, if there is a value in Stock Pieces, use this value. If not, then find last value in Stock Pieces column, add Inbound Pieces, and subtract the Shipped Pieces to achieve the future weeks forecasted value. Then the +2 future date would need to use the previous dates forecasted value, add Inbound Pieces, subtract Shipped Pieces to achieve +2 date forecast value. And so on and so on.

This is simple to do in excel using a reference to the cell above. But I'm struggling to find a way to complete this in Bi. 

Any help would be greatly appreciated. 

[Anonymous]

Hi @Joe_Baltimore ,

I have understand the meaning,  But Dax is not available. And Dax can't do iterations. You can achieve it in Power Query.

I have found a similar post, please refer to if to see if it helps you.

Power Query - Cumulated row, if a certain value is matched 

Best Regards
Community Support Team _ Rongtie

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