Display Date when Value become negative_

[ch_riadh]

Hi, I have this table,

ACCOUNT	DATE	AMOUNT
101	1/2/2020	-500
101	1/17/2020	1000
101	2/25/2020	-200
101	2/29/2020	300
102	2/9/2020	600
102	2/11/2020	700
102	3/21/2020	-800
102	4/26/2020	-900

I want to display from which last date the value change to negative for each ACCOUNT.

ACCOUNT	DATE	AMOUNT
101	2/25/2020	-200
102	3/21/2020	-800

[Kushal_Chawda]

//Front end solution

Dimension:
ACCOUNT

Expressions:
1) DATE
=FirstSortedValue(DATE, - aggr(if(DATE< max(total <ACCOUNT> DATE) and AMOUNT<0,DATE),ACCOUNT,DATE))

2) AMOUNT
=FirstSortedValue(AMOUNT, - aggr(if(DATE< max(total <ACCOUNT> DATE) and AMOUNT<0,DATE),ACCOUNT,DATE))

//Backend solution

Data:
LOAD
ACCOUNT,
"DATE",
AMOUNT
FROM [lib://Web]
(html, utf8, embedded labels, table is @1);

Left Join(Data)
Load ACCOUNT,
     max(DATE) as MaxDate
Resident Data
Group by ACCOUNT;

Left Join(Data)
Load ACCOUNT,
      max(DATE) as DATE,
     1 as IsLatestNegative
Resident Data
where DATE<MaxDate and AMOUNT<0
Group by ACCOUNT;

Then create chart with below dimension & expression

Dimension:
1) ACCOUNT
2) DATE

Expression:
=sum({<IsLatestNegative={1}>}AMOUNT)

[Kushal_Chawda]

Updated solution