cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

## power bi desktop calculate current cell from previous cell in same column

Hi all,

I'm trying to figure out forecast stock level for the quarter by weeks. I have done this by doing this calculation in a new column (on hand + inbound item qty) - max sales qty = forecast stock level. An example is below:

 item code. on hand. inbound items. max sales. date. forecast stock level. A 500 200 250 8/7/22 450 A 500 400 250 15/7/22 650 A 500 0 250 22/7/22 250 A 500 500 250 29/7/22 750 A 500 150 250 5/8/22 400

However, I want the Later Dates to be calculated by (previous stock level row + inbound items) - max sales = forecast stock level. I also want this for multiple item code but for it to be unique to each item code. An example of what i want it to look like below:

 item code. on hand. inbound items. max sales. date. forecast stock level. A 500 200 250 8/7/22 450 A 500 400 250 15/7/22 600 A 500 0 250 22/7/22 350 A 500 500 250 29/7/22 600 A 500 150 250 5/8/22 500

If anyone can help me out I would be very grateful.

Thanks

1 ACCEPTED SOLUTION
Super User

@Nathan__Mox , Suggesting a measure with help from date table

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[On Hand),all('Date')) + CALCULATE(SUM(Table[In Bound]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[outbound]),filter(date,date[date] <=maxx(date,date[date])))

a new column

[on Hand] + sumx(filter(Table, [date] <= earlier([Date]) && [Item code] = earlier([item code]) ), [Inbound] - [outbound ] )

3 REPLIES 3
Super User

@Nathan__Mox , Suggesting a measure with help from date table

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[On Hand),all('Date')) + CALCULATE(SUM(Table[In Bound]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[outbound]),filter(date,date[date] <=maxx(date,date[date])))

a new column

[on Hand] + sumx(filter(Table, [date] <= earlier([Date]) && [Item code] = earlier([item code]) ), [Inbound] - [outbound ] )

Helper I

have you managed to figure out a calculation for my previous issue?

Helper I

hey @amitchandak, thank you for giving me a solution. After puuting the calculation in I realised that the current week or first date (In my example 8/7/22) should actually just equal the on hand value as the on hand value has already had the current weeks (8/7/22) inbound items added and sales subtracted. so at the moment with current calculation im sort of adding twice to current week and subtracting twice. I was hoping you could give me a new calculation to include this please.

If any more context is need let me know.

Thank you

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors