Count only the latest values for filtered string

[Kym_EVO]

Hello,

We have multiple locations and receive enquiries from multiple people each with a unique ID. When their enquiry status is updated it is timestamped and I'm wanting to create a snapshot table that shows me the total of each status as it currently sits so only the most recent status for each location.

I'm thinking I'll need to create a measure for each 'Status' that counts the number of times that status is the latest status per EnquiryId per Location.

This is an example of the data:

Location	EnquiryId	Status	Enquiry Date
Location 1	ID1	Active	11/15/2021 9:53:19 AM
Location 1	ID1	In Progress	11/17/2021 1:09:43 AM
Location 1	ID1	Archived	11/18/2021 1:09:43 AM
Location 1	ID2	Active	11/4/2021 10:59:50 PM
Location 1	ID2	In Progress	11/5/2021 1:24:29 AM
Location 1	ID2	Active	11/16/2021 10:10:20 PM
Location 1	ID2	Enrolled	11/17/2021 3:28:02 AM
Location 1	ID3	Active	11/3/2021 7:21:23 AM
Location 1	ID3	Active	11/3/2021 9:21:16 AM
Location 2	ID4	Active	11/15/2021 11:27:42 AM
Location 2	ID5	Active	10/8/2021 1:44:24 AM
Location 2	ID5	Enrolled	10/8/2021 1:44:58 AM
Location 2	ID6	Active	11/12/2021 8:50:15 AM
Location 2	ID6	In Progress	11/17/2021 10:38:32 PM
Location 2	ID6	Enrolled	11/22/2021 11:45:25 PM

And this would be the resulting table with each column based on a measure:

Location	Current "Active"	Current "In Progress"	Current "Enrolled"	Current "Archived"
Location 1	1	0	1	1
Location 2	1	0	2	0

Any help would be greatly appreciated.

Thanks.

[VahidDM]

Hi @Kym_EVO 

Try this measure:

Measure =
VAR _a =
    SUMMARIZE (
        SUMMARIZE (
            ALL ( 'Table' ),
            'Table'[Location],
            'Table'[EnquiryId],
            "MDate", MAX ( 'Table'[Enquiry Date] )
        ),
        [MDate]
    )
VAR _B =
    CALCULATE (
        COUNTA ( 'Table'[Status] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Location], 'Table'[Status], 'Table'[EnquiryId] ),
            'Table'[Enquiry Date] IN _a
        )
    )
RETURN
    _B

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Kym_EVO]

Thanks very much @ryan_mayu, adding the extra column to show if it was the last date was what I was missing. Appreciate your help.

[ryan_mayu]

you are welcome

[ryan_mayu · Accepted solution]

@Kym_EVO 

you can create a column

IfLast = 
VAR _last=CALCULATE(max('Table'[Enquiry Date]),ALLEXCEPT('Table','Table'[Location],'Table'[EnquiryId]))
return if('Table'[Enquiry Date]=_last,"Y","N")

then create four measures

active = CALCULATE(COUNTROWS('Table'),'Table'[IfLast]="Y",'Table'[Status]="Active")+0

inprogress = CALCULATE(COUNTROWS('Table'),'Table'[IfLast]="Y",'Table'[Status]="In Progress")+0

enrolled = CALCULATE(COUNTROWS('Table'),'Table'[IfLast]="Y",'Table'[Status]="Enrolled")+0

archived = CALCULATE(COUNTROWS('Table'),'Table'[IfLast]="Y",'Table'[Status]="Archived")+0

pls see the attachment below