Count most recent rows only

[req77]

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?

[ichavarria · Accepted solution]

Hi @req77,

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, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly