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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
PBI_1107
Frequent Visitor

Current Ending inventory Calculation followed by cumulative calculation for upcoming months

Hello Community,
I am having trouble finding the DAX Calculation for the Ending Inventory. The calculation goes like this 
1. Current_Ending_Inventory = (Inventory + Transit + Schedules) - ((Forecast * Reference number[)+ Future_orders + Backorders)
This calculation gives me the Ending Inventory for the current Month ( Oct 2023), 
I want a Calculation where for the following month will be like :
Nov 23 Ending Inventory= [Oct 23 Ending Inventory + Oct 23 Schedules] - [Oct 23 Forecast + Oct 23 Future_orders]

Similarly Dec 23 Ending Inventory = [Nov 23 Ending Inventory + Nov 23 Schedules] - [Nov 23 Forecast + Nov23 Future_orders]
So on and so forth.....
Here is the Table for your better understanding:
This table is Based on ID_NO and SHIP_MONTH

PBI_1107_0-1697142148646.png

 

3 REPLIES 3
PBI_1107
Frequent Visitor

Hello @Alef_Ricardo_ , 
I thank you for responding back to the post immediately, 
I have a question regarding the calcualtion you have given. So for "VAR PreviousEndingInv" The [EndingInventory] is it the CurrentEndingInventory or a different calculation, Becuase the values are showing different results.
Please let me know
thanks.

Alef_Ricardo_
Resolver II
Resolver II

To calculate the ending inventory for each month, you can create a DAX measure that accumulates the inventory changes over time. You'll need to use a combination of measures to calculate the ending inventory for each month based on the specified formula. Below is the DAX measure for your requirement:

```DAX
Ending Inventory =
VAR CurrentMonth = MAX('YourTable'[SHIP_MONTH])
VAR PreviousMonth = MAX('YourTable'[SHIP_MONTH]) - 1
VAR CurrentEndingInventory =
CALCULATE(
[Inventory] + [Transit] + [Schedules] - ([Forecast] * [Reference number] + [Future_orders] + [Backorders]),
'YourTable'[SHIP_MONTH] = CurrentMonth
)
VAR PreviousEndingInventory =
CALCULATE(
[Ending Inventory],
'YourTable'[SHIP_MONTH] = PreviousMonth
)
RETURN
IF(
CurrentMonth = MIN('YourTable'[SHIP_MONTH]),
CurrentEndingInventory,
PreviousEndingInventory + [Schedules] - ([Forecast] + [Future_orders])
)
```

Make sure to replace `'YourTable'` with the actual name of your table, and ensure that your table has the necessary columns: SHIP_MONTH, Inventory, Transit, Schedules, Forecast, Reference number, Future_orders, and Backorders.

This measure calculates the ending inventory for each month based on the formula you specified, taking into account the previous month's ending inventory. The IF statement checks if the current month is the minimum month in your table (i.e., the starting point), and if so, it uses the current month's ending inventory. Otherwise, it calculates the ending inventory based on the previous month's ending inventory, schedules, and the forecast and future orders for the current month.

Hello @Alef_Ricardo_ ,
I tired the calculation you gave and the value for the Oct month was correct but for the months after Oct 23 were showing different Values: 

PBI_1107_1-1697648422875.png

But this is How the Actual result should look like:

PBI_1107_0-1697648383819.png

The Calculation with the "PreviousEndingInv" in the Return function gives the accurate result for Oct 23 month but for Nov, dec and so on the visual is showing different values:
This is the calculation I used :
''' DAX

EndingInventory =
VAR CurrentMonth = MAX('New_Table'[SHIP_MONTH].[Date])
VAR PrevMonth = MAX('New_Table'[SHIP_MONTH].[Date])-1
VAR CurrentEndingInv =
([Inventory_Sum] + [Transit_sum] + [Schedules_Sum]) - (([FORECAST]*[Reference number])+[FDO_CPRO_sum] + [Backorders_sum1])
VAR CurrentEndingInv1 =
            CALCULATE(
            CurrentEndingInv , 'New_Table'[SHIP_MONTH] = CurrentMonth)
VAR PreviousEndingInv =
CALCULATE(CurrentEndingInv, 'New_Table'[SHIP_MONTH].[Date] = PrevMonth)
    Return PreviousEndingInv

''''
Please let me know if there is any changes i need to make.
Thanks,
PBI_1107

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.