Count number of ID with Specific Latest Status

[Anonymous]

Hey Datanauts,

I have a table which record the timestamp and change in status of each id as shown below:

Ticket_ID	Edit_Datetime	Change_Status
001	1/5/2020 12:00	New
001	1/6/2020 12:00	Approved
001	1/7/2020 12:00	Investigating
001	1/7/2020 14:00	Closed
002	1/6/2020 12:00	New
002	1/7/2020 13:00	Approved
002	1/7/2020 16:00	Escalated
002	1/9/2020 08:00	Closed

I am trying to count the number of IDs based on thier latest status. For example, if i set the max date slicer to 1/8/2020, I hope to get the following result.

Count of Ticket	Change_Status
1	Escalated
1	Closed

So far I was able to extract the latest status with the following formula but cant get it to count the number of DCR:

Ticket_ID	Edit_Datetime	Change_Status
001	1/7/2020 14:00	Closed
002	1/7/2020 16:00	Escalated

Latest Status = 
var Max_Date= CALCULATE(MAX('Table'[Edit_Datetime]),FILTER(ALLSELECTED('Table'),'Table'[Ticket_ID]=MAX('Table'[Ticket_ID]))) return
CALCULATE(MAX('Table'[Change_Status]), FILTER('Table','Table'[Edit_Datetime] = Max_Date))

[az38]

Hi @Anonymous 

try COUNTROWS() function, like

Latest Status = 
var Max_Date= CALCULATE(MAX('Table'[Edit_Datetime]);FILTER(ALLSELECTED('Table');'Table'[Ticket_ID]=MAX('Table'[Ticket_ID]))) 
return
CALCULATE(COUNTROWS('Table'); FILTER('Table';'Table'[Edit_Datetime] = Max_Date))

do not hesitate to give a kudo to useful posts and mark solutions as solution