Find last text value for groups by date

[Anonymous]

Hi all

I have table like this:

index	ID	to stage	end date	Last Stage
1	aaa	new	7/1/2019	Complete
2	aaa	submitted	7/1/2019	Complete
3	aaa	solutioning	7/3/2019	Complete
4	aaa	accepted	7/5/2019	Complete
5	aaa	Complete	7/6/2019	Complete
6	bbb	new	7/10/2019	Cancelled
7	bbb	submitted	7/11/2019	Cancelled
8	bbb	solutioning	7/13/2019	Cancelled
9	bbb	Cancelled	7/13/2019	Cancelled
10	ccc	new	7/20/2019	solutioning
11	ccc	submitted	7/21/2019	solutioning
12	ccc	solutioning	7/22/2019	solutioning

I need a new calculated column Last Stage with information of ID's groups last stage.

Can someone help me with this?

Thank You.

[Mariusz · Accepted solution]

Hi @Anonymous 

The below DAX expression should return the desired result.

Last Stage = 
VAR _lastIndex = CALCULATE( 
    MAX( 'Table'[index] ), 
    ALLEXCEPT( 'Table', 'Table'[ID] )
)
RETURN CALCULATE( 
    SELECTEDVALUE( 'Table'[to stage] ), 
    ALL( 'Table' ),
    'Table'[index] = _lastIndex 
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

Thank you very much! It works.

Now i have to find out how 🙂