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)
