Forum Discussion
Top 3 table with date filter
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:
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")
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!!
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: