How to find the last value of a date?

[arian]

Hello community, I come with a question that I hope you can help me.

I need to show the last value of a field for a date according to a dimension.

For example, I have these data:

The expected result should be this:

I have tried the following Set Analysis, but it doesn't work:

sum( {< Fecha= {"$(=Date(Max( Aggr( Max( Fecha), Cosecha)), 'YYYY-MM-DD'))"} >} TotalPrecioHecho)

The result of this is this:

I lose values where the date is not Max for the entire data set. The last loaded data is from the date "2020-06-10", but it is not for the 2017/2018 harvest ("Cosecha"), for that it is "2019-11-13".

Can you help me?

Sorry if my English is not very good, I am using Google Translate.

Thanks.

[Saravanan_Desingh]

One script solution is.

tab1:
LOAD * INLINE [
    Fecha, Cosecha, Producto, Total Comprado, Total Precio Hecho
    6/10/2020, 2019/2020, Soja, 25309000, 13023000
    6/3/2020, 2019/2020, Soja, 24453000, 12456000
    11/13/2019, 2019/2020, Soja, 8222000, 4864000
    11/13/2020, 2017/2018, Soja, 38816000, 25234000
    11/6/2019, 2019/2020, Soja, 7786000, 4572000
    11/6/2019, 2017/2018, Soja, 38798000, 25217000
];

Left Join(tab1)
LOAD Cosecha, Date(Max(Fecha)) As Max_Fecha
Resident tab1
Group By Cosecha;

Dimension: =If(Fecha=Max_Fecha,Fecha)

[Saravanan_Desingh]

Output:

[Saravanan_Desingh]

Front end solution:

Dimension: Cosecha

Expressions:

Date(Max(Fecha))

FirstSortedValue([Total Comprado],-Fecha)

FirstSortedValue([Total Precio Hecho],-Fecha)

[arian]

Sorry, but not work. 

The result was this:

Any idea why it doesn't work?

[Saravanan_Desingh]

Please check if the FirstSortedValue return more than one value. In that case, the output will be NULL.