cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## Calculate Daily Balance Qty

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

1 ACCEPTED SOLUTION
Super User

File attached here.

Regards,
Ashish Mathur
http://www.ashishmathur.com
9 REPLIES 9
Helper I

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

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Regards,

TanLC

Super User

File attached here.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

@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])))

Helper I

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

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors