How to sum only the value, for each OrderNumber, of the latest date?

[lucasrcesar]

Hey everyone,

I’m working with a dataset like the one below, where I’ve all the transactions (OrderKey) for each Order (Order Number) and I want to sum the column “Value” to see what is my Revenue, but the Order can have more than one transaction recorded, this happens because the server retries the transaction if the transaction was not completed, as you can see as “OrderNumber_#”.

I have to sum only the value, for each OrderNumber, of the latest date. I did a highlight in the values that I want to sum.

Can someone help me with this matter?

OrderKey	Order Number	Date	Value
11111	11111	01JAN1712:00:00	100
11111_1	11111	01JAN1712:00:40	90
11112	11112	01JAN1712:30:00	300
11122	11122	01JAN1712:34:00	700
11232	11232	01JAN1712:34:00	400
11232_1	11232	01JAN1712:35:00	360
11232_2	11232	01JAN1712:35:10	320
11232_3	11232	01JAN1712:35:55	300
21211	21211	01JAN1715:00:00	1000

So in summary I want a KPI with the SUM of the specific Values, in this case would be 2.390 (90+300+700+300+1000= 2.390)

[sunny_talwar]

May be this

Sum(Aggr(FirstSortedValue(Value, -Date), [Order Number]))

[sunny_talwar]

Or this

Sum(Aggr(FirstSortedValue(Aggr(Sum(Value), [Order Number], Date), -Aggr(Date, [Order Number], Date)), [Order Number]))

[lucasrcesar]

Worked. Thanks Sunny!!