Cumulative Total reset when negative with measure

[adnanarain]

Need help with inventory dashboard i am using following data, total IN and total sales are measures and i want to calculate comulative ending inventory but the column should be reset when we have negative inventory and start over the commulative total.

using following measure but result is not correct issue with end inventory and beg inv for next month should be end inv of last month.

Cumulative Ending Inventory = 
VAR CurrentMonth = max(DateTable[Date]) -- Get the current month context
VAR PreviousMonths = 
    FILTER(
        ALL(DateTable),
        DateTable[Date] <= CurrentMonth
    )

-- Start with the first month's beginning inventory
VAR InitialInventory = [Beginning_Inventory_First_Month]

-- Calculate cumulative inventory change (Total IN + Sales) up to the current month
VAR CumulativeInventoryChange = 
   SUMX(
        PreviousMonths,
        [Total IN] + [Total Sales]
    ) --+ abs([CurrentInventoryChange_PreviousMonth_Dani])

-- Calculate the current month's ending inventory
VAR CurrentEndingInventory = 
    InitialInventory + CumulativeInventoryChange

-- Calculate the previous month's ending inventory
VAR PreviousEndingInventory = 
    CALCULATE(
        IF(
            (InitialInventory + 
            SUMX(
                FILTER(ALL(DateTable), DateTable[Date] < CurrentMonth),
                [Total IN] + [Total Sales]
            )) < 0,  -- If this condition is true (ending inventory is negative)
            [Total IN] + [Total Sales],  -- Use this value if the condition is true (reset to Total IN + Sales)
            InitialInventory + 
            SUMX(
                FILTER(ALL(DateTable), DateTable[Date] < CurrentMonth),
                [Total IN] + [Total Sales] 
            )  -- Use this value if the condition is false (carry forward previous inventory)
        ),
        PREVIOUSMONTH(DateTable[Date])  -- Apply this over the previous month
    )

RETURN 
    IF(
        PreviousEndingInventory < 0,
        MAX(0, [Total IN] + [Total Sales]),  -- Start from 0 with this month's IN and Sales
        MAX(0, CurrentEndingInventory)  -- Otherwise, continue with the cumulative calculation
    )

Period	Beg Inv	Total In	Total Sales	End Inv (Current result)	Expected Output	Item Name
4/1/2023 0:00	13941	6	-942	13005	13005	ATLANTIS 155
5/1/2023 0:00	13005	180	-3172	10013	10013	ATLANTIS 155
6/1/2023 0:00	10013	108	-1538	8583	8583	ATLANTIS 155
7/1/2023 0:00	8583	0	-1160	7423	7423	ATLANTIS 155
8/1/2023 0:00	7423	0	-2885	4538	4538	ATLANTIS 155
9/1/2023 0:00	4538	900	-785	4653	4653	ATLANTIS 155
10/1/2023 0:00	4653	0	-696	3957	3957	ATLANTIS 155
11/1/2023 0:00	3957	1328	-869	4416	4416	ATLANTIS 155
12/1/2023 0:00	4416	0	-396	4020	4020	ATLANTIS 155
1/1/2024 0:00	4020	0	-504	3516	3516	ATLANTIS 155
2/1/2024 0:00	3516	994	-468	4042	4042	ATLANTIS 155
3/1/2024 0:00	4042	24	-628	3438	3438	ATLANTIS 155
4/1/2024 0:00	3438	0	-842	2596	2596	ATLANTIS 155
5/1/2024 0:00	2596	698	-532	2762	2762	ATLANTIS 155
6/1/2024 0:00	2762	0	-460	2302	2302	ATLANTIS 155
7/1/2024 0:00	2302	0	-950	1352	1352	ATLANTIS 155
8/1/2024 0:00	1352	6	-822	536	536	ATLANTIS 155
9/1/2024 0:00	536	0	-731	0	0	ATLANTIS 155	where end inventory is less than 0 then 0 and start cumulative calculation again
10/1/2024 0:00	0	2506	-709	1797	1797	ATLANTIS 155
11/1/2024 0:00	1602	5900	-957	6545	6740	ATLANTIS 155	Column B21 should be 1797 (from last months End inventory)
12/1/2024 0:00	6545	0	-751	5794	5989	ATLANTIS 155	Column B22 should be 6740 (from last months End inventory)
4/1/2023 0:00	3030	700	-60	3670	3670	DFR7
5/1/2023 0:00	3670	0	-1000	2670	2670	DFR7
6/1/2023 0:00	2670	0	-720	1950	1950	DFR7
7/1/2023 0:00	1950	0	0	1950	1950	DFR7
8/1/2023 0:00	1950	0	-501	1449	1449	DFR7
9/1/2023 0:00	1449	0	-1000	449	449	DFR7
10/1/2023 0:00	449	0	0	449	449	DFR7
11/1/2023 0:00	449	1300	-500	1249	1249	DFR7
12/1/2023 0:00	1249	500	-500	1249	1249	DFR7
1/1/2024 0:00	1249	700	-504	1445	1445	DFR7
2/1/2024 0:00	1445	0	-5	1440	1440	DFR7
3/1/2024 0:00	1440	550	-9	1981	1981	DFR7
4/1/2024 0:00	1981	500	-215	2266	2266	DFR7
5/1/2024 0:00	2266	600	-454	2412	2412	DFR7
6/1/2024 0:00	2412	0	-588	1824	1824	DFR7
7/1/2024 0:00	1824	0	-738	1086	1086	DFR7
8/1/2024 0:00	1086	0	-951	135	135	DFR7
9/1/2024 0:00	135	0	-610	0	0	DFR7
10/1/2024 0:00	0	1000	-620	380	380	DFR7
11/1/2024 0:00	380	400	-660	0	120	DFR7
12/1/2024 0:00	0	0	-610	0	0	DFR7

[v-zhengdxu-msft]

Hi @adnanarain 

Please try this:

You can open the Advanced editor and paste these code in it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZVNagQhEIWvEmadgfrVchkIgUDIIpndkPtfI22p1cO09qJx8z2rtN6z7/cLAfEV5Ap4eb0gF6lr2r5rEdqWt9vX2/ft8/cFVS9/r0OgQwCgdTWoEsZ8okldA4Dsq1UNKttak5vG1KrEiyAmWAusCbJQCMhM14LSBNLaKOCSfKJA6IqkUSKVtOax8Vw0+5WRn9tSWUuolxBMowQvS9ThdR4IBq8ga77vz+r7l1LRq6TVIDYFjwruCnJBohNBtxSL36u3ZEtHSTiK1M+Zil+S8omi+4lyolFBltaQ8BIxBF/0hLfhcaWRCaOTfrqTlGNmmXGJDxuBHxp8+wwrT0jYaHN/7UG7U5upFpI+5qSi0ZKuWtqfAQaubG4V/ErfP37yMf6ccvhti/UEHDN6ADNNuD4bbAMZ3xNkB2hzOR65PggUKafdRZKDmzD4yNT3rpWdkP22kRzVFbnHtZMZHgL7RFIcJSaoR4qDqoRqc8aRG898Mdw7JJzsONJIydPYSNFJh2PCgpEqNTtyY8Lmb0ePx4QbQwaLGBV9HvKeti2d8Qjj8aofU9Y9sD1bE2480v4jkw4erL9nCmZV//4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, #"Beg Inv" = _t, #"Total In" = _t, #"Total Sales" = _t, #"Item Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Beg Inv", Int64.Type}, {"Total In", Int64.Type}, {"Total Sales", Int64.Type}, {"Period", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Item Name"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Period", "Beg Inv", "Total In", "Total Sales", "Index"}, {"Period", "Beg Inv", "Total In", "Total Sales", "Index"}),
    AddOutputColumn = Table.AddColumn(#"Expanded Count", "output", each List.Accumulate(
        Table.SelectRows(#"Expanded Count", (row) => row[Index] <= _[Index] and row[Item Name] = _[Item Name]) [Index],
        0,
        (state, current) =>
            let
                currentItem = Table.SelectRows(#"Expanded Count", (row) => row[Index] = current and row[Item Name] = _[Item Name]),
                currentValues = currentItem{0},
                previousOutput = if current = 1 then currentValues[Beg Inv] else state,
                newOutput = previousOutput + currentValues[Total In] + currentValues[Total Sales],
                finalOutput = if newOutput < 0 then 0 else newOutput
            in
                finalOutput
        ), type number)
in
    AddOutputColumn

It's worth noting that you only need to copy #"Grupedros" and the following  rows, the above does not need to be changed.

The result is as follow:

Best Regards

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

[adnanarain]

calculation take ages to complete when i implementated on real data as total sales and total in are also calculation based on the values from 2 different tables.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[lbendlin]

Conditional accumulation is generally not supported in DAX.  For very small datasets you may be able to get away with identifying the last time inventory dipped, and start from there.  For all other scenarios your only option is Power Query's List.Accumulate which allows conditional accumulation.

You could also argue that when you sell more than you have inventory then you cannot just set that inventory to zero - a negative inventory correctly represents your backlog.