Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create range based on previous row

I have this database with columns A and B and I would like to create columns C and D (START, END), in DAX or Power Query.   Column D would just repeat column B, but column C should be ordered...
DAX
Help
Power Query
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Anonymous 
    If you wish to use DAX you may try

    Start =
MAXX (
    FILTER (
        CALCULATETABLE (
            VALUES ( 'Table'[VALUE] ),
            ALLEXCEPT ( 'Table', 'Table'[ITEM] )
        ),
        'Table'[Value] < EARLIER ( 'Table'[Value] )
    ),
    'Table'[Value]
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi Anonymous 
If you wish to use DAX you may try

Start =
MAXX (
    FILTER (
        CALCULATETABLE (
            VALUES ( 'Table'[VALUE] ),
            ALLEXCEPT ( 'Table', 'Table'[ITEM] )
        ),
        'Table'[Value] < EARLIER ( 'Table'[Value] )
    ),
    'Table'[Value]
)