Calculate Daily Balance Qty

[Tan_LC]

Hi, in table below, in order to get the Daily Balance (kg), the calculation should be Stock Available (kg) deduct the Usage.

If the Stock Available (kg) is blank, then it should use the previous day balance to continue deduct the Usage until the Stock Available (kg) with value available. 

Kindly assist.

Thanks.

Regards,

TanLC

[Tan_LC]

In below is the excel:

Prod Date	Usage	Stock Available (kg)	Daily Balance (kg) [Desired Outcome]
01/03/2022	1,097.68	82,875.00	81,763.24
02/03/2022	1,111.76	80,875.00	79,750.41
03/03/2022	1,124.59		78,617.14
04/03/2022	1,133.27		77,438.31
05/03/2022	1,178.83		76,237.49
06/03/2022	1,200.82		75,114.50
07/03/2022	1,122.99		74,022.83
08/03/2022	1,091.67		72,951.64
09/03/2022	1,071.19		71,878.47
10/03/2022	1,073.17		70,749.43
11/03/2022	1,129.04		69,618.96
12/03/2022	1,130.47		68,480.08
13/03/2022	1,138.88		67,274.72
14/03/2022	1,205.36		66,025.74
15/03/2022	1,248.98		64,764.99
16/03/2022	1,260.75		63,506.29
17/03/2022	1,258.70	62,875.00	61,590.05
18/03/2022	1,284.95	62,875.00	61,639.29
19/03/2022	1,235.71		60,390.60
20/03/2022	1,248.69		59,149.89
21/03/2022	1,240.71		57,927.54
22/03/2022	1,222.35	53,875.00	53,875.00

Thanks.

Regards,

TanLC

[Ashish_Mathur]

Please exlplain how you have arrived at the figures in the Desired outcome column.

[Tan_LC]

@Ashish_Mathur ,

Please refer column E for the calculation. Thanks.

Column A	Column B	Column C	Column D	Column E
Prod Date	Usage	Stock Available (kg)	Daily Balance (kg) [Desired Outcome]
01/03/2022	1,097.68	82,875.00	81,763.24	=C3-B4
02/03/2022	1,111.76	80,875.00	79,750.41	=C4-B5
03/03/2022	1,124.59		78,617.14	=D4-B6
04/03/2022	1,133.27		77,438.31	=D5-B7
05/03/2022	1,178.83		76,237.49	=D6-B8
06/03/2022	1,200.82		75,114.50	=D7-B9
07/03/2022	1,122.99		74,022.83	=D8-B10
08/03/2022	1,091.67		72,951.64	=D9-B11
09/03/2022	1,071.19		71,878.47	=D10-B12
10/03/2022	1,073.17		70,749.43	=D11-B13
11/03/2022	1,129.04		69,618.96	=D12-B14
12/03/2022	1,130.47		68,480.08	=D13-B15
13/03/2022	1,138.88		67,274.72	=D14-B16
14/03/2022	1,205.36		66,025.74	=D15-B17
15/03/2022	1,248.98		64,764.99	=D16-B18
16/03/2022	1,260.75		63,506.29	=D17-B19
17/03/2022	1,258.70	62,875.00	61,590.05	=C19-B20
18/03/2022	1,284.95	62,875.00	61,639.29	=C20-B21
19/03/2022	1,235.71		60,390.60	=D20-B22
20/03/2022	1,248.69		59,149.89	=D21-B23
21/03/2022	1,240.71		57,927.54	=D22-B24
22/03/2022	1,222.35	53,875.00	53,875.00	=C24-B25

Regards,

TanLC

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Tan_LC]

@Ashish_Mathur ,

Thanks for your help, however the link given is not accessible.

Please advice.

Regards,

TanLC

[Ashish_Mathur · Accepted solution]

File attached here.

[amitchandak]

@Tan_LC , Sunch calculation should always done cumulative manner

example measure

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date'))   - CALCULATE(SUM(Table[usage]),filter(date,date[date] <=maxx(date,date[date])))

[Tan_LC]

Dear Sir,

Thanks on the feedback. However, error message pop up as below.

Besides, I've further make clear on my desired outcome value as below using excel. Hope you can assist.

Thanks.

Regards,

TanLC

[Ashish_Mathur]

Hi,

Share the table in a format that can be pasted in an MS Excel file.