"All time high" KPI with value and date

[danabb]

Hello,

I have my data in the following format:

event	day	month	year	timestamp
1	1	11	2021	2021-11-01 10:25
2	1	11	2021	2021-11-01 14:38
3	1	11	2021	2021-11-01 18:32
4	3	11	2021	2021-11-03 15:12
5	3	11	2021	2021-11-03 19:28
6	6	11	2021	2021-11-06 06:08
7	6	11	2021	2021-11-06 03:22
8	9	11	2021	2021-11-09 18:10
9	21	11	2021	2021-11-21 14:46

I would like to show a a "Max events in one day" KPI together with the date of the (first) occurrence.

I figured I can find the maximum number of events with the following formula:

=Max(Aggr(Count(DISTINCT event_Id), day, month, year))

However, how do I find the date (as year & month & day) where the "all time high" was reached?

[AshutoshBhumkar · Accepted solution]

Hello,

Try using,

=FirstSortedValue(timestamp,-Aggr(Count(DISTINCT event), day, month, year)) in KPI.

Thanks,
Ashutosh