Cummulative Total by Date that doesn't go negative

[carted01]

Hi,

I have a report that takes a daily forecast of sales by product, and combines it with incoming stock, in order to produce a forecast Stock on Hand for any given date in the future.

I have managed to create a "Forecast SOH" in the below table using the below formula. The problem however is when stock is depleted to zero, the forecast sales keep depleting the Forecast SOH more and more into the negative. In the real world, however, in spite of the forecast sales, zero sales are occuring because there is zero stock on hand to sell. The result I am chasing is the "Desired Forecast SOH" column, whereby once stock hits zero, it stays on zero until a postive "Net Stock Movement" occurs.

Any advice on how to acheive this would be greatly appreciated.

Forecast SOH =

CALCULATE(

    SUM('Incoming Stock'[Net Stock Movement]),

    FILTER(

        ALLSELECTED('Incoming Stock'[Date]),

        ISONORAFTER('Incoming Stock'[Date], MAX('Incoming Stock'[Date]), DESC)

    )

)

Date	Net Stock Movement	Forecast SOH	Desired Forecast SOH
22/04/2022	0	0	0
23/04/2022	0	0	0
24/04/2022	0	0	0
25/04/2022	0	0	0
26/04/2022	-324	-324	0
27/04/2022	-101	-425	0
28/04/2022	-17	-442	0
29/04/2022	0	-442	0
30/04/2022	0	-442	0
1/05/2022	0	-442	0
2/05/2022	-119	-561	0
3/05/2022	-32	-593	0
4/05/2022	678	85	678
5/05/2022	739	824	1417
6/05/2022	0	824	1417
7/05/2022	0	824	1417
8/05/2022	0	824	1417
9/05/2022	-199	625	1218
10/05/2022	-32	592	1186
11/05/2022	-78	515	1108
12/05/2022	-13	502	1095
13/05/2022	0	502	1095
14/05/2022	0	502	1095
15/05/2022	0	502	1095
16/05/2022	-279	223	816
17/05/2022	-32	190	784
18/05/2022	-78	112	706
19/05/2022	-13	99	693
20/05/2022	1008	1107	1701
21/05/2022	0	1107	1701
22/05/2022	0	1107	1701
23/05/2022	-119	988	1582
24/05/2022	-65	924	1517
25/05/2022	-156	768	1361
26/05/2022	-26	742	1335
27/05/2022	0	742	1335
28/05/2022	0	742	1335
29/05/2022	0	742	1335
30/05/2022	-78	664	1258
31/05/2022	437	1101	1695
1/06/2022	-152	949	1542
2/06/2022	-25	923	1517
3/06/2022	0	923	1517

[v-xiaotang]

Hi @carted01 

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

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

[Whitewater100]

Hi:

Does something like this make sense?

Stock Calc = 

VAR SOHcheck = CALCULATE(Forecast SOH, DATEADD(Dates,Date, -1, DAY)
return
IF(AND( SOHcheck <0, Incoming stock +[Forecast SOH] < 0), BLANK, [Forecast SOH])
))

* I don't know your incoming stock measure.

Basically seeing if your Forecast SOH was negative yesterday and despite any new stock coming in to cover forecasted sales, it is still negative, then BLANK, otherwise use your Forecast SOH measure. Something like this.

I hope this helps directionally..

[tamerj1]

Hi @carted01 

you may tey this measure

Forecast SOH =
VAR IerationTable =
    FILTER (
        ALLSELECTED ( 'Incoming Stock' ),
        'Incoming Stock'[Date] <= MAX ( 'Incoming Stock'[Date] )
            && 'Incoming Stock'[Net Stock Movement] > 0
    )
RETURN
    SUMX ( IerationTable, 'Incoming Stock'[Net Stock Movement] )

[carted01]

Thank you for the suggestion. Isn't quite getting the result however. It seems to use the positive figures only in all circumstances, however it does need to deplete with the negatives where the end result is greater than zero:

Date	Net Stock Movement	Forecast SOH (Tamerj1)	Desired SOH
22/04/2022	0		0
23/04/2022	0		0
24/04/2022	0		0
25/04/2022	0		0
26/04/2022	-324		0
27/04/2022	-101		0
28/04/2022	-17		0
29/04/2022	0		0
30/04/2022	0		0
1/05/2022	0		0
2/05/2022	-119		0
3/05/2022	-32		0
4/05/2022	678	678	678
5/05/2022	739	1417	1417
6/05/2022	0	1417	1417
7/05/2022	0	1417	1417
8/05/2022	0	1417	1417
9/05/2022	-199	1417	1218
10/05/2022	-32	1417	1186
11/05/2022	-78	1417	1108
12/05/2022	-13	1417	1095
13/05/2022	0	1417	1095
14/05/2022	0	1417	1095
15/05/2022	0	1417	1095
16/05/2022	-279	1417	816
17/05/2022	-32	1417	784
18/05/2022	-78	1417	706
19/05/2022	-13	1417	693
20/05/2022	1008	2425	1701
21/05/2022	0	2425	1701
22/05/2022	0	2425	1701
23/05/2022	-119	2425	1582
24/05/2022	-65	2425	1517
25/05/2022	-156	2425	1361

[carted01]

Thank you for the suggestion, unfortunately it does not acheive the desired forecast. See table below.

As an FYI if it helps provide some clarity, the excel formula used in the below table (in E3 and filling down) is =IF(E2+B3<0,0,E2+B3)

Date	Net Stock Movement	Forecast SOH	Forecast SOH (Amit)	Desired SOH
22/04/2022	0	0	0	0
23/04/2022	0	0	0	0
24/04/2022	0	0	0	0
25/04/2022	0	0	0	0
26/04/2022	-324	-324	-324	0
27/04/2022	-101	-425	-425	0
28/04/2022	-17	-442	-442	0
29/04/2022	0	-442	-442	0
30/04/2022	0	-442	-442	0
1/05/2022	0	-442	-442	0
2/05/2022	-119	-561	-561	0
3/05/2022	-32	-593	-593	0
4/05/2022	678	85	678	678
5/05/2022	739	824	739	1417
6/05/2022	0	824	0	1417
7/05/2022	0	824	0	1417
8/05/2022	0	824	0	1417
9/05/2022	-199	625	-199	1218
10/05/2022	-32	592	-32	1186
11/05/2022	-78	515	-78	1108
12/05/2022	-13	502	-13	1095
13/05/2022	0	502	0	1095
14/05/2022	0	502	0	1095
15/05/2022	0	502	0	1095
16/05/2022	-279	223	-279	816
17/05/2022	-32	190	-32	784
18/05/2022	-78	112	-78	706
19/05/2022	-13	99	-13	693
20/05/2022	1008	1107	1008	1701
21/05/2022	0	1107	0	1701
22/05/2022	0	1107	0	1701
23/05/2022	-119	988	-119	1582
24/05/2022	-65	924	-65	1517
25/05/2022	-156	768	-156	1361
26/05/2022	-26	742	-26	1335
27/05/2022	0	742	0	1335
28/05/2022	0	742	0	1335
29/05/2022	0	742	0	1335
30/05/2022	-78	664	-78	1257
31/05/2022	437	1101	437	1694
1/06/2022	-152	949	-152	1542
2/06/2022	-25	923	-25	1517
3/06/2022	0	923	0	1517
4/06/2022	0	923	0	1517
5/06/2022	0	923	0	1517
6/06/2022	0	923	0	1517
7/06/2022	-102	822	-102	1415
8/06/2022	478	1299	478	1893
9/06/2022	-25	1274	-25	1868
10/06/2022	0	1274	0	1868
11/06/2022	0	1274	0	1868
12/06/2022	0	1274	0	1868
13/06/2022	-76	1198	-76	1792
14/06/2022	-64	1134	-64	1728
15/06/2022	-152	982	-152	1576
16/06/2022	-25	956	-25	1551
17/06/2022	0	956	0	1551
18/06/2022	0	956	0	1551
19/06/2022	0	956	0	1551
20/06/2022	806	1762	806	2357
21/06/2022	-64	1699	-64	2293
22/06/2022	-152	1546	-152	2141
23/06/2022	-25	1521	-25	2116
24/06/2022	0	1521	0	2116
25/06/2022	0	1521	0	2116
26/06/2022	0	1521	0	2116
27/06/2022	-76	1445	-76	2040
28/06/2022	-64	1381	-64	1976
29/06/2022	-152	1229	-152	1824
30/06/2022	-25	1203	-25	1799
1/07/2022	0	1203	0	1799
2/07/2022	0	1203	0	1799
3/07/2022	0	1203	0	1799
4/07/2022	-77	1127	-77	1722
5/07/2022	-64	1063	-64	1658
6/07/2022	-154	909	-154	1504
7/07/2022	-26	883	-26	1478
8/07/2022	756	1639	756	2234
9/07/2022	0	1639	0	2234
10/07/2022	0	1639	0	2234
11/07/2022	-77	1563	-77	2157
12/07/2022	-64	1499	-64	2093
13/07/2022	-154	1345	-154	1939
14/07/2022	-26	1319	-26	1913
15/07/2022	756	2075	756	2669
16/07/2022	0	2075	0	2669
17/07/2022	0	2075	0	2669
18/07/2022	-77	1999	-77	2592
19/07/2022	-64	1935	-64	2528
20/07/2022	-154	1781	-154	2374
21/07/2022	-26	1755	-26	2348
22/07/2022	1260	3015	1260	3608
23/07/2022	0	3015	0	3608
24/07/2022	0	3015	0	3608
25/07/2022	-77	2939	-77	3531
26/07/2022	-64	2875	-64	3467
27/07/2022	-154	2721	-154	3313
28/07/2022	-26	2695	-26	3287
29/07/2022	0	2695	0	3287
30/07/2022	0	2695	0	3287
31/07/2022	0	2695	0	3287
1/08/2022	-90	2606	-90	3197
2/08/2022	-75	2531	-75	3122
3/08/2022	-180	2351	-180	2942
4/08/2022	-30	2321	-30	2912
5/08/2022	0	2321	0	2912
6/08/2022	0	2321	0	2912
7/08/2022	0	2321	0	2912
8/08/2022	-90	2232	-90	2822
9/08/2022	-75	2157	-75	2747
10/08/2022	-180	1977	-180	2567
11/08/2022	-30	1947	-30	2537
12/08/2022	0	1947	0	2537
13/08/2022	0	1947	0	2537
14/08/2022	0	1947	0	2537
15/08/2022	-90	1858	-90	2447
16/08/2022	-75	1783	-75	2372
17/08/2022	-180	1603	-180	2192
18/08/2022	-30	1573	-30	2162
19/08/2022	0	1573	0	2162
20/08/2022	0	1573	0	2162
21/08/2022	0	1573	0	2162
22/08/2022	-90	1484	-90	2072
23/08/2022	-75	1409	-75	1997
24/08/2022	-180	1229	-180	1817
25/08/2022	-30	1199	-30	1787
26/08/2022	0	1199	0	1787
27/08/2022	0	1199	0	1787
28/08/2022	0	1199	0	1787
29/08/2022	792	1992	792	2579
30/08/2022	-75	1917	-75	2504
31/08/2022	-180	1737	-180	2324
1/09/2022	-34	1703	-34	2290
2/09/2022	0	1703	0	2290
3/09/2022	0	1703	0	2290
4/09/2022	0	1703	0	2290
5/09/2022	-103	1600	-103	2187
6/09/2022	-86	1514	-86	2101
7/09/2022	-206	1308	-206	1895
8/09/2022	-34	1274	-34	1861
9/09/2022	0	1274	0	1861
10/09/2022	0	1274	0	1861
11/09/2022	0	1274	0	1861
12/09/2022	-103	1171	-103	1758
13/09/2022	-86	1085	-86	1672
14/09/2022	-206	879	-206	1466
15/09/2022	-34	844	-34	1432
16/09/2022	0	844	0	1432
17/09/2022	0	844	0	1432
18/09/2022	0	844	0	1432
19/09/2022	-103	741	-103	1329
20/09/2022	-86	655	-86	1243
21/09/2022	-206	449	-206	1037
22/09/2022	-34	415	-34	1003
23/09/2022	0	415	0	1003
24/09/2022	0	415	0	1003
25/09/2022	0	415	0	1003
26/09/2022	0	415	0	1003
27/09/2022	-137	278	-137	866
28/09/2022	-206	72	-206	660
29/09/2022	-34	37	-34	626
30/09/2022	0	37	0	626
1/10/2022	0	37	0	626
2/10/2022	0	37	0	626
3/10/2022	-87	-50	-50	539
4/10/2022	-73	-123	-123	466

[amitchandak]

@carted01 , Try like

Forecast SOH =

var _1 = CALCULATE(
SUM('Incoming Stock'[Net Stock Movement]),
FILTER(
ALLSELECTED('Incoming Stock'[Date]),
ISONORAFTER('Incoming Stock'[Date], MAX('Incoming Stock'[Date]), DESC)
)
)
var _max = minx(filter(values('Incoming Stock'[Date]) , _1 >0), 'Incoming Stock'[Date])
return CALCULATE(
SUM('Incoming Stock'[Net Stock Movement]),
FILTER(
ALLSELECTED('Incoming Stock'[Date]),
ISONORAFTER('Incoming Stock'[Date], MAX('Incoming Stock'[Date]), DESC) && 'Incoming Stock'[Date] >= _max
)