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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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