How to create a column in a table with the next value of a column?

[tahechadv_2022]

Guys I have the following table:

I want to create a Sankey visual, the STAGE_NAME column would be the SOURCE column, and I want to create the DESTINATION column.

But in order to create this column, I need a DAX code to iterate over each DEAL_ID and capture the next STAGE_NAME based on the column DATE_CREATE. Every time the DEAL_ID changes, I won't need the next phase.

Trying to make it clear, all need all the phases (STAGE_NAME) that a card (DEAL_ID) passed in cronological order. Following the order of the sankey visual: SOURCE and DESTINATION. My problem is: how to create the DESTINATION column.

Link of the table:

https://drive.google.com/file/d/1mi0dAjGnKbxHa7zn7CM9zx7YjyfxeDOy/view?usp=sharing

[smpa01 · Accepted solution]

@tahechadv_2022  you can use a measure like this which doubles as a calculated column too.

Measure 2 = 
CALCULATE (
    MAX ( 'Table'[stagename] ),
    ALL ( 'Table' ),
    OFFSET (
        1,
        ALL ( 'Table'[dealId], 'Table'[date_create] ),
        ORDERBY ( 'Table'[date_create], ASC ),
        ,
        PARTITIONBY ( 'Table'[dealId] )
    )
)

[tahechadv_2022 · Accepted solution]

Hi guys, based on another dax query of mine I found another way to do the same thing:

TO =

CALCULATE (

    MAX ('stage_history'[STAGE_NAME]),

    FILTER (

        ALLEXCEPT ( 'stage_history', 'stage_history'[DEAL_ID] ),

        'stage_history'[DATE_CREATE] > EARLIER ( 'stage_history'[DATE_CREATE] )

    )

)

[CheenuSing]

Hi

With the sample data csv file, what is the final output expected.

Cheers

CheenuSing

[tahechadv_2022]

I want to create a column named TO, this column must contain the name of the next STAGE_NAME according to the cronology of DATE_CREATE. 

The last DEAL_ID must be empty, because this card didn't move anywhere else.

[smpa01 · Accepted solution]

@tahechadv_2022  you can use a measure like this which doubles as a calculated column too.

Measure 2 = 
CALCULATE (
    MAX ( 'Table'[stagename] ),
    ALL ( 'Table' ),
    OFFSET (
        1,
        ALL ( 'Table'[dealId], 'Table'[date_create] ),
        ORDERBY ( 'Table'[date_create], ASC ),
        ,
        PARTITIONBY ( 'Table'[dealId] )
    )
)

[tahechadv_2022]

Amazing, thank-you!