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 )
Greg_Deckler
Community Champion
Community ChampionNaveenMD See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
tamerj1Community Champion
Community ChampionGreg_Deckler
This is a nice example of pseudo-recursive calculation that might be helpful to you for your new book.