Forum Discussion
Counting the Most Recent Status
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.
Hi Anonymous ,
please check if that helps you.
3 Replies
Hi Anonymous ,
please check if that helps you.
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.
Hi Anonymous ,
can you share some screenshots of the data model and matrix?