Measure to Show latest Status that updates based on Date Slicer

[jmowen]

Hello,

I was wondering if it is possible to have a measure in a table dynamically update based on the date slicer?

What I would like to achieve is for every ID find the latest status and apply that to every row with that ID.

And then have that update everytime a date slicer is used.  

Data

ID	Date	Status
1	30/01/2019	A
1	4/04/2019	A
1	21/02/2020	B
1	23/02/2022	B
2	3/03/2020	A
2	4/07/2020	B
2	5/08/2021	C
2	9/03/2022	A
3	5/12/2019	C
3	4/04/2020	C
3	6/03/2021	B
3	23/07/2021	B
4	21/03/2019	A
4	22/04/2020	B
4	14/07/2021	C
4	2/02/2022	A
4	2/02/2022	A

Expected Outcome

ID	Date	Status	Latest Status
1	30/01/2019	A	B
1	4/04/2019	A	B
1	21/02/2020	B	B
1	23/02/2022	B	B
2	3/03/2020	A	A
2	4/07/2020	B	A
2	5/08/2021	C	A
2	9/03/2022	A	A
3	5/12/2019	C	B
3	4/04/2020	C	B
3	6/03/2021	B	B
3	23/07/2021	B	B
4	21/03/2019	A	A
4	22/04/2020	B	A
4	14/07/2021	C	A
4	2/02/2022	A	A
4	2/02/2022	A	A

Expected Outcome when slicer is set to max date of 31/12/2021

ID	Date	Status	Latest Status
1	30/01/2019	A	B
1	4/04/2019	A	B
1	21/02/2020	B	B
2	3/03/2020	A	C
2	4/07/2020	B	C
2	5/08/2021	C	C
3	5/12/2019	C	B
3	4/04/2020	C	B
3	6/03/2021	B	B
3	23/07/2021	B	B
4	21/03/2019	A	C
4	22/04/2020	B	C
4	14/07/2021	C	C

[amitchandak · Accepted solution]

@jmowen , Try a measure like

calculate(LASTNONBLANKVALUE(Table[Date], max(Table[Status])), filter(allselected(Table), Table[ID] = max(Table[ID])))