Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
File attached here.
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
Please exlplain how you have arrived at the figures in the Desired outcome column.
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
Hi,
You may download my PBI file from here.
Hope this helps.
Thanks for your help, however the link given is not accessible.
Please advice.
Regards,
TanLC
@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])))
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
Hi,
Share the table in a format that can be pasted in an MS Excel file.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |