Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Working with the following dataset:
Customer | Date | Qty |
A | 31/3/2019 | 0.9 |
A | 31/3/2019 | 0.2 |
A | 30/6/2019 | 0.2 |
A | 30/6/2019 | 0.2 |
B | 30/6/2018 | 0.6 |
B | 30/9/2018 | 0.4 |
B | 30/9/2018 | 0.2 |
We want to get following result:
Customer | Last Date | Last Qty |
A | 30/6/2019 | 0.4 |
B | 30/9/2018 | 0.6 |
We have resolved Last Date measure with following code:
Max({<Date =>} Date )
But we have problems to create Last Qty measure, we are trying with following code:
Sum({<Date = {"$(=Max({<Date =>} Date ))"}>} [Qty])
But it calculates max date globally, not by customer.
Aditionally, these measures should ignore date selection.
Thanks in advance!
Try:
FirstSortedValue( {<Date>} Aggr({<Date>} Sum({<Date>} Qty), Customer, Date), -Aggr(Date,Date))
If I may, I think we should ignore selection of Date within Aggr(Date, Date) also. and I am not sure if this is important or not, but I usually use the same Aggr() dimensions as the first part.
FirstSortedValue({<Date>} Aggr(Sum({<Date>} Qty), Customer, Date), -Aggr(Only({<Date>}Date), Customer, Date))