Dear all, I have been battling with this problem for many days now and would like to ask for some help on how to solve.
I have a table of data which contains the costs of products depending on the date I bought them :
| Date | Item | Cost |
| 01/01/2019 | A | 1 |
| 01/02/2019 | A | 2 |
| 01/03/2019 | A | 3 |
| 01/02/2019 | B | 6 |
| 01/05/2019 | B | 9 |
| 01/07/2019 | B | 3 |
| 01/04/2019 | C | 5 |
| 01/05/2019 | C | 7 |
| 01/06/2019 | C | 5 |
I am trying to create a table to show the costs of the products showing the price of the first buy and the last buy of each one of them:
| Item | Initial Cost | Final Cost |
| A | 1 | 3 |
| B | 6 | 3 |
| C | 5 | 5 |
I am trying to use this two functions ( FirstSortedValue and aggr) but it won´t work for me, I would need some help.
This is an example of how I am using the functions:
FirstSortedValue( distinct Cost, aggr(Max( Cost), Item, Date),1) and FirstSortedValue( distinct Cost, -aggr(Max( Cost), Item, Date),1)
But it won´t give me the first and last value, It gives me tha maximun and minimun values.
Thank you very much in advance, I hope you can give me some assistance with this.
