Forum Discussion
Solved
Top 3 table with date filter
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 b...
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:
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!
sevenhills Won't this only return the highest sales though, rather than sale at latest date?
Yes, it will calculate the highest sales for each dealer name, and get the date of sale for the highest sales.