Skip to main content
cancel
Showing results for 
Search instead 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

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 ] )

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 ] )

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
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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