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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors