Projected Inventory Calculation

[zjm]

From the screenshot above. I do have  5 calculation:

IMS = sum(ims)

Shipment Plan = sum(shipment)

Actual Ending Inventory = SUM('IMS Inventory'[Value])

Ending Projected Inventory = [Ending Inventory Previous Month]-'IMS DATA EFE'[IMS]+[Shipment Plan]

Ending Inventory Previous Month = CALCULATE('IMS Inventory'[Ending Inventory],DATEADD('Calendar Table'[Date],-1,MONTH),ALL('Calendar Table'[Date].[Year],'Calendar Table'[Date].[Month],'Calendar Table'[Date].[MonthNo]),ALL('Calendar Table'[Month],'Calendar Table'[Period]))

I have actual ending inventory for January to Aug 2023, i need to show the Ending Projected Inventory from Sept to december. For P9 (September) I have correct value because the actual ending inventory has data for august (P8). But in october onwards, there is no data so the values are wrong. 

I need to transpose the Ending projected inventory of P9 to P10. The expected computation for that will be Ending projected Inventory last month (P9) - IMS (P10) + Shipment Plan (P10) and so on for the rest of the months without Actual Ending Inventory.

[Anonymous]

Hi @zjm ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[123abc]

To achieve the desired computation in Power BI DAX, you can create a measure that calculates the Ending Projected Inventory for the months where there is no actual ending inventory available. You can use the LASTNONBLANK function to get the last available ending inventory value and then project the inventory for the subsequent months based on the shipment plan and IMS data.

Here's how you can create the measure:

Ending Projected Inventory =
VAR LastMonthInventory = CALCULATE(MAX('IMS Inventory'[Ending Inventory]),
FILTER(ALL('Calendar Table'),
'Calendar Table'[Year] = MAX('Calendar Table'[Year]) &&
'Calendar Table'[MonthNo] = MAX('Calendar Table'[MonthNo]) - 1))
VAR ShipmentPlan = SUM('Shipment'[Shipment])
VAR IMS = SUM('IMS DATA EFE'[IMS])
RETURN
IF(ISBLANK([Actual Ending Inventory]),
LastMonthInventory - IMS + ShipmentPlan,
[Actual Ending Inventory])

This measure does the following:

1. It calculates the last available ending inventory for the previous month using the MAX function and FILTER to get the last non-blank value.
2. It sums up the shipment plan and IMS for the current month.
3. If the actual ending inventory is blank (indicating no data available), it computes the projected ending inventory using the formula you provided: LastMonthInventory - IMS + ShipmentPlan.
4. If there is actual ending inventory available, it returns the actual ending inventory for the current month.

Ensure that you adjust the table and column names according to your actual Power BI data model. This measure should be placed in your model and used in visualizations to display the projected ending inventory for the months without actual ending inventory data.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.