Using Previous rows result for next row calculation

[Anonymous]

Hi all,

I have the following measures

Current OH	The current inventory at warehouse
Sell-In Forecast	Forecast of what we might send to ware house
Sell through Forecast	Forecast of what might be sold from Warehouse to customer

This is my Data.

Month	Current OH	OH Forecast	Sell-In Forecast	Sell though Forecast
Jan-20	137206	137,206.0
Feb-20		181,790.0	111947	67,363.0
Mar-20		288,076.0	181119	74,833.0
Apr-20		278,401.0	86543	96,218.0
May-20		308,773.0	131204	100,832.0
Jun-20		265,326.0	62255	105,702.0
Jul-20		222,076.0	66076	109,326.0
Aug-20		235,699.0	101716	88,093.0
Sep-20		213,996.0	62855	84,558.0
Oct-20		239,317.0	112545	87,224.0
Nov-20		233,404.0	88387	94,300.0
Dec-20		242,813.0	94145	84,736.0

Iam trying create a forecast inventory at warehouse

My formual is = Previous Months Inventory+ Sell-In Forecast - Sell through Forecast

for example Inventory forecast for Feb = Jan Inventory (137206) + Sell-In Forecast(Feb 2020) - Sell through Forecast(Feb 2020) and so on for other months. 

I want to use the forecast of Febraury to calculate march

How do I do it? 

[pedrocampos]

I have a similar problem.
Can somebody help me?

Hi

In this table presented below I have two columns, being: "Saldo_Inicial" and "Saldo_Final".

In the first row, the "Saldo_Final" column shows a value that was calculated from an initial value of 5000000 in the "Saldo_Inicial" column.
However, from the second line down I need the value shown in the "Saldo_Inicial" column to always be the calculated value in the "Saldo_Final" column of the previous line.

How do I do that?

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Table:

DateTable(a calculated table):

DateTable = CALENDARAUTO()

There is a one-to-one relationship between two tables.

You may create measures as follows.

Sell through Forecast measure = SUM('Table'[Sell through Forecast])

Sell-In Forecast measure = SUM('Table'[Sell-In Forecast])

OH Forecast = 
IF(
    ISBLANK(MAX('Table'[Date])),
    BLANK(),

        CALCULATE(SUM('Table'[Current OH]),ALL('Table'))+
        SUMX(
            DATESYTD(DateTable[Date]),
            'Table'[Sell-In Forecast measure]-'Table'[Sell through Forecast measure]
        )
)

Result:

Best Regards

Allan

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

[amitchandak]

Assuming you want to calc OH Forecast

OH Forecast Calc = CALCULATE(sumx(OHForecast,OHForecast[Current OH]-OHForecast[Sell though Forecast]+OHForecast[Sell-In Forecast]),DATESYTD('Date'[Date]))

link : https://www.dropbox.com/s/4atbx9936hxu4ni/OHForecast.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin