Top 3 table with date filter

[diego-jd]

Hello, I've been trying to get a table with top 3 Dealer with most sales at the latest date but can't make it work.

My data is like this
   Dealer Name
   Sales
   upload_datetime

And I've been trying to use RANKX to no avail.

I also tried to create a simple table with a TOP N filter in Sales, and a filter where upload_datetime = latest_date 
(latest date being a measure but can't filter it as expected.)

So I created a column called latest_date which would basically take 1 if the date was the latest, 0 otherwise

latest_date_mex = LASTDATE(at_mex[upload_datetime])   -> measure

latest_date = IF(at_mex[upload_datetime] = [latest_date_mex], 1, 0)  -> column

but it would take 1 in every date.
Any ideas how can I do this seemingly easy task? Thanks!

[sevenhills]

Try this Rank Measure:

Rank = RANKX(
           ALLEXCEPT('Table','Table'[Dealer Name]), 
           CALCULATE(
              SUM([Sales]),
              ALLEXCEPT('Table','Table'[Dealer Name], 'Table'[Upload_DateTime]))
       ,,DESC,Dense)

If upload date time is date time field, then you format the column (or copy of the column) as date only and use that one!

and apply the visual filter for the visual only as TOP 1

Hope this helps!

[ExcelMonke]

@sevenhills Won't this only return the highest sales though, rather than sale at latest date?

[sevenhills]

Yes, it will calculate the highest sales for each dealer name, and get the date of sale for the highest sales.

[ExcelMonke]

Hello,
Whilst this solution is not pretty, it will get you what you need. I created a mock data table, with columns Name, Date, and Sales: 

Next, I created a calculated column for the table:

MaxDate = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Name]))

From here, I created the following measure to return only sales at the Max date:

Sales at Max Date = 
VAR _Sales = VALUE(CALCULATE(MAX('Table'[Sales]),FILTER('Table','Table'[Date]='Table'[MaxDate])))
VAR _SumTable =
SUMMARIZE('Table','Table'[Name],"Sales",_Sales)

RETURN
SUMX(_SumTable,_Sales)

Next, create a table with the Name and Measure in the Values (in the screenshot I also included max date to validate the data)

Lastly, filter the Name to TOPN 3, based on Sales at Max date value to get your final result:

I think there are more elegant solutions out there, but this will at least get you what you need. The Sales at Max Date measure can be truncated to just the variable, but I added in the SUMX clause in order to capture multiple sales on the same max date, and so that the Total Sales of the Top 3 calculated correctly.

[diego-jd]

Hi ExcelMonke and thank you for your time.

It seems that this solution would take the latest date for each Dealer, right? 

What if, in your example, I want to obtain the top sales for the latest, aggregated, date. I.e. 1/25/2024

[ExcelMonke]

My pleasure!
Yes, this solution takes the latest date for each dealer, and the sales at that date. As you can see in the example, Dealer "B"'s highest sales is 70, however, their sales at the latest date is 10 (01/24/2024 vs. 01/25/2024).

If you want the top sales for the latest, aggregated, date you can consider the following measure:

Sales at Max Date = 
VAR _AggDate = FORMAT(AVERAGEX('Table',VALUE('Table'[MaxDate])),"MM/DD/YYYY")
VAR _Sales = VALUE(CALCULATE(MAX('Table'[Sales]),FILTER('Table','Table'[Date]=_AggDate)))
VAR _SumTable =
SUMMARIZE('Table','Table'[Name],"Sales",_Sales)

RETURN
SUMX(_SumTable,_Sales)

Now, do keep in mind that this is taking the mean average of all your max dates (which is what I understood from your "latest, aggregated, date" comment). In the example data, this date is equal to 1/20/2024 (see measure below on how I arrived there). The problem is though, that no sales occured during that date, so your top 3 will return 0 dealers. This may not be a problem if your dataset is large enough and you have sales pretty much everyday. Just something to keep in mind.

AggMaxDate = 
FORMAT(AVERAGEX('Table',VALUE('Table'[MaxDate])),"MM/DD/YYYY")

[diego-jd]

Sorry for the confusion, by aggregated date I meant get the latest date in the entire dataset and use that as the filter, like this:

thanks!!

[ExcelMonke · Accepted solution]

No problem! Much easier to do that 🙂 you can ignore the previous DAX suggestions.

One option to consider (you will still have to use the visual level filter "trick" to get your top 3):

Measure = 
VAR _MaxDate = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Date]))

RETURN
CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Date]=_MaxDate))

This is my result: