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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Nathan__Mox
Helper I
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.
A5002002508/7/22450
A50040025015/7/22650
A500025022/7/22250
A50050025029/7/22750
A5001502505/8/22400

 

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.
A5002002508/7/22450
A50040025015/7/22600
A500025022/7/22350
A50050025029/7/22600
A5001502505/8/22500

 

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

 

Thanks

1 ACCEPTED SOLUTION
amitchandak
Super User
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 ] )

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

3 REPLIES 3
amitchandak
Super User
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 ] )

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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