Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Material QTY | Rate | Closing Stock | Date | |
150000 | 0.2 | 150000 | 11-Apr-23 | |
0 | 0.2 | 120000 | 12-Apr-23 | |
0 | 0.2 | 96000 | 13-Apr-23 | |
0 | 0.2 | 76800 | 14-Apr-23 | |
0 | 0.2 | 61440 | 15-Apr-23 | |
0 | 0.2 | 49152 | 16-Apr-23 | |
0 | 0.2 | 39322 | 17-Apr-23 | |
0 | 0.2 | 31457 | 18-Apr-23 | |
0 | 0.2 | 25166 | 19-Apr-23 | |
100000 | 0.15 | 106391 | 20-Apr-23 | |
0 | 0.15 | 90432 | 21-Apr-23 | |
0 | 0.15 | 76867 | 22-Apr-23 | |
0 | 0.15 | 65337 | 23-Apr-23 | |
0 | 0.15 | 55537 | 24-Apr-23 |
How do we calculate the closing stock based on the values in the column Material Qty
The formula used in excel is given as =(C2+A3)*(1-B3).
Solved! Go to Solution.
Hi @NaveenMD
Please refer to attached sample file with the proposed solution
Closing Stock =
VAR MinDate = MINX ( FILTER ( 'Table', 'Table'[Material QTY] > 0 ), 'Table'[Date] )
VAR T1 = FILTER ( 'Table', 'Table'[Date] <= EARLIER ( 'Table'[Date] ) )
RETURN
SUMX (
T1,
VAR Qty = 'Table'[Material QTY]
VAR T2 = FILTER ( T1, 'Table'[Date] >= EARLIER ( 'Table'[Date] ) )
VAR Rate =
PRODUCTX (
T2,
IF ( 'Table'[Date] = MinDate, 1, (1 - 'Table'[Rate] ) )
)
RETURN
Qty * Rate
)
I was able to identify one caviate with this formula
Material QTYRateDateClosing Stock
0 | 0.2 | 11 April 2023 | 0 |
0 | 0.2 | 12 April 2023 | 0 |
0 | 0.2 | 13 April 2023 | 0 |
0 | 0.2 | 14 April 2023 | 0 |
0 | 0.2 | 15 April 2023 | 0 |
0 | 0.2 | 16 April 2023 | 0 |
150000 | 0.2 | 17 April 2023 | 120000 |
0 | 0.2 | 18 April 2023 | 96000 |
0 | 0.2 | 19 April 2023 | 76800 |
100000 | 0.15 | 20 April 2023 | 150280 |
0 | 0.15 | 21 April 2023 | 127738 |
0 | 0.15 | 22 April 2023 | 108577 |
0 | 0.15 | 23 April 2023 | 92291 |
0 | 0.15 | 24 April 2023 | 78447 |
Lets say on 17th April if we add 150000 then it should be reflected as 150000 not 120000. 120000 should be the value for next day.
Excel Formula :=((1-0.2)*E2)+A3
Material QTY | Rate | Date | Closing Stock | Closing Stock 2 |
0 | 0.2 | 11-Apr-23 | 0 | 0 |
0 | 0.2 | 12-Apr-23 | 0 | 0 |
0 | 0.2 | 13-Apr-23 | 0 | 0 |
0 | 0.2 | 14-Apr-23 | 0 | 0 |
0 | 0.2 | 15-Apr-23 | 0 | 0 |
0 | 0.2 | 16-Apr-23 | 0 | 0 |
150000 | 0.2 | 17-Apr-23 | 120000 | 150000 |
0 | 0.2 | 18-Apr-23 | 96000 | 120000 |
0 | 0.2 | 19-Apr-23 | 76800 | 96000 |
100000 | 0.15 | 20-Apr-23 | 150280 | 176800 |
0 | 0.15 | 21-Apr-23 | 127738 | 141440 |
0 | 0.15 | 22-Apr-23 | 108577 | 113152 |
0 | 0.15 | 23-Apr-23 | 92291 | 90521.6 |
0 | 0.15 | 24-Apr-23 | 78447 | 72417.28 |
Please help
Hi @NaveenMD
Please refer to attached sample file with the proposed solution
Closing Stock =
VAR MinDate = MINX ( FILTER ( 'Table', 'Table'[Material QTY] > 0 ), 'Table'[Date] )
VAR T1 = FILTER ( 'Table', 'Table'[Date] <= EARLIER ( 'Table'[Date] ) )
RETURN
SUMX (
T1,
VAR Qty = 'Table'[Material QTY]
VAR T2 = FILTER ( T1, 'Table'[Date] >= EARLIER ( 'Table'[Date] ) )
VAR Rate =
PRODUCTX (
T2,
IF ( 'Table'[Date] = MinDate, 1, (1 - 'Table'[Rate] ) )
)
RETURN
Qty * Rate
)
I was able to identify one caviate with this formula
Material QTYRateDateClosing Stock
0 | 0.2 | 11 April 2023 | 0 |
0 | 0.2 | 12 April 2023 | 0 |
0 | 0.2 | 13 April 2023 | 0 |
0 | 0.2 | 14 April 2023 | 0 |
0 | 0.2 | 15 April 2023 | 0 |
0 | 0.2 | 16 April 2023 | 0 |
150000 | 0.2 | 17 April 2023 | 120000 |
0 | 0.2 | 18 April 2023 | 96000 |
0 | 0.2 | 19 April 2023 | 76800 |
100000 | 0.15 | 20 April 2023 | 150280 |
0 | 0.15 | 21 April 2023 | 127738 |
0 | 0.15 | 22 April 2023 | 108577 |
0 | 0.15 | 23 April 2023 | 92291 |
0 | 0.15 | 24 April 2023 | 78447 |
Lets say on 17th April if we add 150000 then it should be reflected as 150000 not 120000. 120000 should be the value for next day.
Excel Formula :=((1-0.2)*E2)+A3
Material QTY | Rate | Date | Closing Stock | Closing Stock 2 |
0 | 0.2 | 11-Apr-23 | 0 | 0 |
0 | 0.2 | 12-Apr-23 | 0 | 0 |
0 | 0.2 | 13-Apr-23 | 0 | 0 |
0 | 0.2 | 14-Apr-23 | 0 | 0 |
0 | 0.2 | 15-Apr-23 | 0 | 0 |
0 | 0.2 | 16-Apr-23 | 0 | 0 |
150000 | 0.2 | 17-Apr-23 | 120000 | 150000 |
0 | 0.2 | 18-Apr-23 | 96000 | 120000 |
0 | 0.2 | 19-Apr-23 | 76800 | 96000 |
100000 | 0.15 | 20-Apr-23 | 150280 | 176800 |
0 | 0.15 | 21-Apr-23 | 127738 | 141440 |
0 | 0.15 | 22-Apr-23 | 108577 | 113152 |
0 | 0.15 | 23-Apr-23 | 92291 | 90521.6 |
0 | 0.15 | 24-Apr-23 | 78447 | 72417.28 |
Please help
I cannot test on my laptop as I don't have access to it right now but you may try
Closing Stock =
VAR MinDate =
MINX ( FILTER ( 'Table', 'Table'[Material QTY] > 0 ), 'Table'[Date] )
VAR T1 =
FILTER ( 'Table', 'Table'[Date] <= EARLIER ( 'Table'[Date] ) )
RETURN
SUMX (
T1,
VAR Qty = 'Table'[Material QTY]
VAR T2 =
FILTER ( T1, 'Table'[Date] >= EARLIER ( 'Table'[Date] ) )
VAR Rate =
PRODUCTX ( T2, IF ( 'Table'[Date] = MinDate, 1, ( 1 - 'Table'[Rate] ) ) )
RETURN
Qty * Rate
)
Thanks
Thank you for the solution this seems to work.
User | Count |
---|---|
59 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
87 | |
59 | |
52 | |
37 | |
21 |