DAX Row minus Previous Row

BrianJ · July 31, 2020, 2:34pm

OK, RankX-based index works like a champ.

DAX Index = 
RANKX(
    ALL( 'Table' ),
    [Date],,
    ASC,
    Dense
)

Retrieving the previous value just requires a couple of tweaks to our prior Previous Value measure - creation of a new variable to track our current row position, and referencing DAX Index instead of the PQ-created index we used before:

Previous Value2 =

VAR SelRow = 'Table'[DAX Index]

VAR PrevIndx =
CALCULATE(
    MAX( 'Table'[DAX Index] ),
    FILTER(
        'Table',
        [DAX Index] < SelRow
    ),
   ALL( 'Table'[DAX Index] )
)

VAR  Result =
CALCULATE(
    MAX( 'Table'[Total Qty] ),
    FILTER(
        'Table',
        'Table'[DAX Index] = PrevIndx
    )
)

RETURN
Result

And now this provides the same result as before, but should be compatible with DIrect Query limitations:

Hope this gets you what you need - shout back if it doesn’t. Full solution file posted below.

Brian
eDNA Forum - Previous Value Solution - Take 2.pbix (20.4 KB)

P.S. This construct is worth tucking in your back pocket, since it’s a good, flexible method for traversing columns that can be adapted for a wide variety of situations w/o the use of EARLIER. If you look in the SQLBI DAX guide, they recommend this approach even when EARLIER is available…