Forum Discussion
Helper IInventory risk based on expiry dates
I want to calculate my risk for my inventory based on expiry dates for this invnetory and expted sales
Assume today is April 07
for Product A:
April sales = 150 units / 30 days X 23 days remaining = 115 units
inventory lot a1 will be sold
inventory lota2 willsell 65 units ; 435 units remain at the end of april in this lot.
May Sales 150 units
Lot a2 will have 285 untis left at the end of May
This lot expires June 01 the 285 needs to show as risk on June 01
June sales 250 units
Lot a3 will sell - no issues
how do i calculate the running inventory showing at end of april 635 units in inventory
June 01 285 expiry and only 200 unit in inventory ?
similar for product B and C.
Thank you
Hi GGerritsen ,
Thank you for being a part of Microsoft Fabric Community Forum!Thanks so much for sharing all the details. At this point, since we’ve explored a lot of possibilities and the issue seems to go a bit deeper, I think the best next step would be to raise a support ticket with Microsoft. That way, the support team can take a closer look with the right tools and internal access.
To raise a support ticket for Fabric and Power BI, kindly follow the steps outlined in the following guide:
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn
If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.
Regards,
Menaka Kota.
20 Replies
- bhanu_gautam
Super User
Create measures to calculate the running inventory at the end of each month.
dax
RunningInventory =
VAR CurrentMonth = MAX(Forecast[Month])
VAR SalesToDate = CALCULATE(SUM(Forecast[Sales]), Forecast[Month] <= CurrentMonth)
VAR InitialInventory = CALCULATE(SUM(Inventory[Quantity]), Inventory[Item] = Forecast[Item])
RETURN InitialInventory - SalesToDateCreate measures to calculate the risk based on expiry dates.
dax
Risk =
VAR CurrentMonth = MAX(Forecast[Month])
VAR ExpiryDate = CALCULATE(MAX(Inventory[Expiry Date]), Inventory[Item] = Forecast[Item])
VAR InventoryAtRisk = CALCULATE(SUM(Inventory[Quantity]), Inventory[Expiry Date] <= ExpiryDate)
RETURN IF(CurrentMonth >= ExpiryDate, InventoryAtRisk, 0)Use the measures to create visualizations in Power BI to show the running inventory and risk for each product.
GGerritsenI have 2 seperate tables, one with inventory (qty / exppiry date) and one with the forecast. keep having errors with this part of the formula :
Inventory[Item] = Forecast[Item]
error ""The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression."
I tried to add a column on the inventory table with a "true" expression if the item is on the forecast (which it always does).
v-menakakotaCommunity Support
Hi GGerritsen ,
Thank you for reaching out to us on the Microsoft Fabric Community Forum.
When you are trying to use Inventory[Item] = Forecast[Item] inside a Calculate filter expression, it throws an error because you can't directly reference two different tables like that without using a row context or functions like Related.
But since both Inventory and Forecast are connected through Item as an active relationship, you dont have to explicitly give Inventory[Item] = Forecast[Item].
You can simply use Sum(Inventory[Quantity])
RunningInventory =
VAR CurrentMonth = MAX(Forecast[Month])
VAR SalesToDate = CALCULATE(SUM(forecast[Sales]), Forecast[Month] <= CurrentMonth)
VAR InitialInventory = sum(Inventory[Quantity])
If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
- GGerritsen
Runnig into a challenge, have seen this a few times, now might be the time to get it resolved.
I have 2 excel files. 1 is inventory (has the item number / lot / quatity and expiry date) the order file has the forecast (item number forecast month and forecast qty)
when i am trying to enter the DAX formula i cannot get the link in the formula to the forecast file for the item.
In de model view i do have the relationship between the 2 files with the item number
