Counting the Most Recent Status

[Anonymous]

The target is to count the most recent activity status by date. I currently have COUNTROWS of a table and that is correctly giving me all status categorized by dates but there is a new requirement only asking for a total count of the most recent activity status. Any suggestions on how to structure a measure that will result in a count of the most recent activity status?

Below is sample data: table 1 = source data / table 2 = expected output

(Table 1 = source data)

Date	Store_ID	Activity_Status
Wednesday, October 28, 2020	1	New
Thursday, October 29, 2020	1	In Progress
Friday, October 30, 2020	1	Complete
Friday, January 29, 2021	2	New
Monday, February 1, 2021	3	New
Monday, February 1, 2021	4	New
Tuesday, February 2, 2021	3	In Progress

(Table 2 = expected output)

Month_Year	Most_Recent_ActivityStatus_Count
October 2020	1
January 2021	1
February 2021	2

Your advice will be greatly appreciated. 

[mwegener · Accepted solution]

Hi @Anonymous ,

please check if that helps you.

[Anonymous]

Thank you for your support. I was able to figure out a measure to give me the count of the most recent activity with the following: 

Store ID Count = COUNTROWS(DataTable)

Most Recent Activity Count =
CALCULATE(
[Store ID Count],
FILTER(
DataTable,
MAX(DataTable[Store_ID])
),
LASTDATE(DataTable[Date])
)

My problem now is that when I drop this information into a matrix table, the individual activities are still visable. Although the count is correct, is there a way for me to not have the individual activities visable? 

Your advice is greatly appreciated. 

[mwegener]

Hi @Anonymous ,
can you share some screenshots of the data model and matrix?