Forum Discussion
NaveenMD
Helper I
Helper IDynamically referencing above rows in the same column
Hi Community, How can we reference the row above using dax Material QTY Rate Date X Closing Stock Correct Closing stock Index 0 0.2 11-Apr-23 0 0 0 0 0.2 12-Apr-23 0 0 1 ...
NaveenMD
Here you goClosing Stock 2 = VAR T1 = FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ) RETURN SUMX ( T1, VAR Qty = 'Table'[Material QTY] VAR T2 = FILTER ( T1, 'Table'[Index] >= EARLIER ( 'Table'[Index] ) ) VAR Rate = PRODUCTX ( T2, IF ( 'Table'[Index] = EARLIEST ( 'Table'[Index] ), 1, ( 1 - 'Table'[Rate] ) ) ) RETURN Qty * Rate )
NaveenMDHelper I
Helper IIf you see the date 19 April 23 the Material Quantity (96800) should be multiplied by 0.8 ie. (1-0.2) and the result should be 77440 but when we use the above mentioned DAX the Material quantity gets multiplied by 0.85 ie. (1-1.5) and the result optained is 82280.
Somehow the the material quantity of 19th April gets multiplied by rate of 20th April.
DAX:
Closing Stock 2 =
VAR MinIndex = MINX ( FILTER ( 'Table', 'Table'[Material QTY] > 0 ), 'Table'[Index] )
VAR T1 = FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) )
RETURN
SUMX (
T1,
VAR Qty = 'Table'[Material QTY]
VAR T2 = FILTER ( T1, 'Table'[Index] > EARLIER ( 'Table'[Index] ) )
VAR Rate =
COALESCE(
PRODUCTX (
T2,
1 - 'Table'[Rate]
),
1
)
RETURN
Qty * Rate
)Do let me know if any other clarificaion is required
Thanks tamerj1
