Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
bdpaasch
Advocate I
Advocate I

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 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?

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@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 )

 

DataInsights_0-1656777761285.png

 

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
)

 

DataInsights_1-1656777880532.png

 

The MIN/TOPN measure you tried doesn't work because TOPN returns a table, not a column.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
DataInsights
Super User
Super User

@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 )

 

DataInsights_0-1656777761285.png

 

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
)

 

DataInsights_1-1656777880532.png

 

The MIN/TOPN measure you tried doesn't work because TOPN returns a table, not a column.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.