get date based on max aggr measure in chart footnote

[ioannaiogr]

Hi all, hope you're doing great.

My question today is the following.

I want to provide in the footnote of my  line chart something like:

'The maximum count of all time is ' &max(aggr(count distinct id,date))' on date '&???'

So I need the date when this maximum happened... How do I do that?

Many thanks in advance

[justISO · Accepted solution]

Hi, maybe:

FirstSortedValue(date,-aggr(count(distinct id),date))

[ioannaiogr]

Hi all again,

my question is following the above. I noticed that there is the same count twice on two different dates. How should I handle it? Do you know how to show them both (or all possible dates)? 

Thank you in advance.

[justISO]

this should work with even more than 2 max dates:

concat(distinct aggr(if(count(distinct id) = max(total aggr(count(distinct id), date)), date), date), ', ')