Forum Discussion
Most recent three dates
Greetings all, I’m trying to create three measures that identify the three most recent dates in a table. The table is appended queries, the query is run every 3 – 5 weeks. One of the fields in th...
Anonymous,
There are various ways to achieve this. This approach uses a calculated column in table Distinct Dates:
Run Date Rank = RANKX ( 'Distinct Dates', 'Distinct Dates'[Run Date],, DESC, Dense )Measures:
Most Recent Run Date = CALCULATE ( MAX ( 'Distinct Dates'[Run Date] ), 'Distinct Dates'[Run Date Rank] = 1 )Second Most Recent Run Date = CALCULATE ( MAX ( 'Distinct Dates'[Run Date] ), 'Distinct Dates'[Run Date Rank] = 2 )Third Most Recent Run Date = CALCULATE ( MAX ( 'Distinct Dates'[Run Date] ), 'Distinct Dates'[Run Date Rank] = 3 )The MIN/TOPN measure you tried doesn't work because TOPN returns a table, not a column.
DataInsights
Super User
Super UserAnonymous,
There are various ways to achieve this. This approach uses a calculated column in table Distinct Dates:
Run Date Rank = RANKX ( 'Distinct Dates', 'Distinct Dates'[Run Date],, DESC, Dense )
Measures:
Most Recent Run Date =
CALCULATE (
MAX ( 'Distinct Dates'[Run Date] ),
'Distinct Dates'[Run Date Rank] = 1
)Second Most Recent Run Date =
CALCULATE (
MAX ( 'Distinct Dates'[Run Date] ),
'Distinct Dates'[Run Date Rank] = 2
)Third Most Recent Run Date =
CALCULATE (
MAX ( 'Distinct Dates'[Run Date] ),
'Distinct Dates'[Run Date Rank] = 3
)
The MIN/TOPN measure you tried doesn't work because TOPN returns a table, not a column.