Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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
Solved! Go to Solution.
@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 ] )
@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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
77 | |
63 | |
51 | |
47 |
User | Count |
---|---|
212 | |
82 | |
61 | |
60 | |
57 |