topic Re: Dynamically Referencing above rows in DAX Commands and Tips

Dynamically Referencing above rows

NaveenMD — Tue, 11 Apr 2023 10:58:31 GMT

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).

Re: Dynamically Referencing above rows

tamerj1 — Thu, 13 Apr 2023 04:38:14 GMT

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 )

Re: Dynamically Referencing above rows

NaveenMD — Wed, 12 Apr 2023 04:46:55 GMT

Thank you for the solution this seems to work.

Re: Dynamically Referencing above rows

NaveenMD — Wed, 12 Apr 2023 12:19:16 GMT

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

Re: Dynamically Referencing above rows

tamerj1 — Wed, 12 Apr 2023 12:35:41 GMT

NaveenMD

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
)

Re: Dynamically Referencing above rows

NaveenMD — Thu, 13 Apr 2023 03:30:37 GMT

Thanks