Latest date with data in other field

[gbernabo]

Hi all, I'm trying to show in as a KPI chart the latest date in which other field has data greater than zero.

This is what I have:

date	things
nov-19	3
dec-19	8
jan-20	13
feb-20	24
mar-20	36
apr-20	0
may-20	0
jun-20	0
jul-20	0

In this example I'd need to show "mar-20" , which is the latest date where "things" has a value greater than zero.

Any ideas? I've tried to no avail:

Max(if ([things] = 0, date))

Thanks in advance.

[kaushiknsolanki · Accepted solution]

Try this in KPI object.

=Date(FirstSortedValue(date,things*-1))

Make sure the "date" field is a proper Date field, meaning it is not a text value.

[gbernabo]

Thanks, that worked!

IDK why, but Max(if ([things] > 0, date)) also works..