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 ISorry for the concern tamerj1 !
Excel Formula : =D2*(1-B2)+A3
| Material QTY | Rate | Date | Correct Closing stock |
| 0 | 0.2 | 11-Apr-23 | 0 |
| 0 | 0.2 | 12-Apr-23 | 0 |
| 0 | 0.2 | 13-Apr-23 | 0 |
| 0 | 0.2 | 14-Apr-23 | 0 |
| 0 | 0.2 | 15-Apr-23 | 0 |
| 0 | 0.2 | 16-Apr-23 | 0 |
| 150000 | 0.2 | 17-Apr-23 | 150000 |
| 1000 | 0.2 | 18-Apr-23 | 121000 |
| 0 | 0.2 | 19-Apr-23 | 96800 |
| 100000 | 0.15 | 20-Apr-23 | 177440 |
| 0 | 0.15 | 21-Apr-23 | 150824 |
| 0 | 0.15 | 22-Apr-23 | 128200.4 |
| 0 | 0.15 | 23-Apr-23 | 108970.34 |
| 0 | 0.15 | 24-Apr-23 | 92624.789 |
You can use the link for accessing the file : https://docs.google.com/spreadsheets/d/1E8obh8vJHHI6OX8FSEzTQ4_iM4OSGdQY/edit?usp=sharing&ouid=103277306777121863015&rtpof=true&sd=true
tamerj1
Community Champion
Community ChampionNaveenMD
Here you go
Closing 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
)