Lead Lag Dax in Power BI desktop

[Anonymous]

Hello,

I need help in creating a dax to get date assigned and date reassigned to users according to the unique work ID.

This is how the table looks like now:

Unique ID	Date Assigned	User
1	21/05/2020	Tom
1	29/05/2020	Rick
2	21/05/2020	Dan
2	10/06/2020	Rick
3	21/05/2020	Tom
3	29/05/2020	Rick
3	10/06/2020	Dan

I want to get a date reassigned column which should be the assigned date of the next person for the same unique id:

Unique ID	Date	User	Date reassigned
1	21/05/2020	Tom	29/05/2020
1	29/05/2020	Rick	null
2	21/05/2020	Dan	10/06/2020
2	10/06/2020	Rick	null
3	21/05/2020	Tom	29/05/2020
3	29/05/2020	Rick	10/06/2020
3	10/06/2020	Dan	null

Thanks.

[amitchandak]

@Anonymous , Try a new measure like

Date reassigned=
calculate(max(Table[Date Assigned]), filter(All(Table) , Table[Unique ID]=max(Table[Unique ID]) && Table[Date Assigned]>max(Table[Date Assigned])))

[parry2k · Accepted solution]

@Anonymous I didn't check if @amitchandak  solution will work or not but I will avoid using FILTER function as it can be a performance hit when you are working on a large dataset.

You can use following to add measure or column

Column = 
VAR __date = CALCULATE ( MAX ( Data[Date Assigned] ) )
RETURN 
CALCULATE ( 
    MIN ( Data[Date Assigned] ), 
    ALLEXCEPT ( Data, Data[Unique ID] ), 
    Data[Date Assigned] > __date 
) 

Measure  = 
VAR __date = MAX ( Data[Date Assigned] )
RETURN 
CALCULATE ( 
    MIN ( Data[Date Assigned] ), 
    ALLEXCEPT ( Data, Data[Unique ID] ), 
    Data[Date Assigned] > __date 
) 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[parry2k]

@Anonymous what defines the next person, is it alphabet or what or in the order you shown the data? Have to have some way of sorting to define the next person.

[Anonymous]

@parry2k  sorting by the unique ID and date assigned is good to get the next reassigned date.