Forum Discussion
Count most recent rows only
Hi!
I have a table with this structure:
| CODZ | LEVEL | DATE |
| X | A111 | 14/03/2023 |
| X | A222 | 13/03/2023 |
| Z | A444 | 14/03/2023 |
| Y | A777 | 14/03/2023 |
| Y | A333 | 13/03/2023 |
| Y | A777 | 12/03/2023 |
I want to create a measure that counts only the number of rows with the most recent date based on the date filter that the user makes in the dashboard.
For example:
- if the user does not filter the dashboard, the measure must show the number of rows from the most recent date in the table (14/03/2023). The measure should bring the value: 3
- if the user only selects the day 13/03/2023 in the dashboard date filter, this measure should bring the value: 2
How can I do a measure like that?
Hi Anonymous,
You can create a measure that calculates the maximum date based on the current filter context and then counts the number of rows that match that maximum date. Here's an example measure that should work for your table:
MaxDateCount =
VAR MaxDate = MAX('Table'[DATE])
RETURN
COUNTROWS(
FILTER('Table', 'Table'[DATE] = MaxDate)
)This measure first uses the MAX function to calculate the maximum date based on the current filter context. It then uses the FILTER function to count the number of rows in the table where the date column matches the maximum date.
To use this measure, add it to your visual and make sure that the date column is included in the visual as well. The measure should automatically update based on any date filters that are applied.
Best regards,
If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly
1 Reply
Hi Anonymous,
You can create a measure that calculates the maximum date based on the current filter context and then counts the number of rows that match that maximum date. Here's an example measure that should work for your table:
MaxDateCount =
VAR MaxDate = MAX('Table'[DATE])
RETURN
COUNTROWS(
FILTER('Table', 'Table'[DATE] = MaxDate)
)This measure first uses the MAX function to calculate the maximum date based on the current filter context. It then uses the FILTER function to count the number of rows in the table where the date column matches the maximum date.
To use this measure, add it to your visual and make sure that the date column is included in the visual as well. The measure should automatically update based on any date filters that are applied.
Best regards,
If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly