Most recent three dates

[bdpaasch]

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 the appended table is the query date. That query run date is repeated many times since the query brings back lots of records.

The most recent query run date was easy, the MAX function worked fine. The next two most recent dates have me stuck.

To simplify my battle, I made a helper table of only the query run date field using the DISTINCT function.

So now I have a little, one-column helper table called Distinct Dates, with one column “Run Date”.

The most recent run date measure is still easy, the max function still works fine. For the next most recent date measure, I’m trying to combine the MIN and TOPN functions (maybe I’m over-engineering this?):

Second Most Recent run date = MIN(TOPN(2,'Distinct Dates','Distinct Dates'[Run Date]))

The error message back is that MIN “only accepts a column reference”. I thought the TOPN function creates a virtual column. (For the third most recent date, I'd change the 2 to 3.) This seems like it should be easy, but I’m failing!

Help please?

[DataInsights · Accepted solution]

@bdpaasch,

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.