I've got a fairly simple table with two measures Sum(ITEMS) and Above(Sum(ITEMS)) displayed against a REF_DATE dimension which works fine however for obvious reasons when you filter to a single REF_DATE the Above calculation doesn't return any values (there is only one row so it can't return the row above).
I've got two asks (I'm guessing the second one is more problematic, currently I'll be happy with any help I can get)
- When I select a single REF_DATE I need to know what the result for the previous date would have been.
- If I select two non-consecutive dates I want the previous date to be the previous week, not the earlier of the two selected dates (in the table below if I select 14/10/2022 and 04/11/2022 I still want the results for 04/11/2022 to be 72 and 65).
So my table is...
| REF_DATE |
Sum(ITEMS) |
Above(Sum(ITEMS)) |
| 14/10/2022 |
40 |
- |
| 21/10/2022 |
54 |
40 |
| 28/10/2022 |
65 |
54 |
| 04/11/2022 |
72 |
65 |
based on the following data...
| REF_DATE |
CUSTOMER |
ORDER_ID |
ITEMS |
| 14/10/2022 |
Cust A |
ABC123 |
0 |
| 14/10/2022 |
Cust A |
BCD234 |
15 |
| 14/10/2022 |
Cust B |
CDE345 |
20 |
| 14/10/2022 |
Cust B |
EFG456 |
5 |
| 21/10/2022 |
Cust A |
ABC123 |
11 |
| 21/10/2022 |
Cust A |
BCD234 |
13 |
| 21/10/2022 |
Cust B |
CDE345 |
14 |
| 21/10/2022 |
Cust B |
EFG456 |
16 |
| 28/10/2022 |
Cust A |
ABC123 |
20 |
| 28/10/2022 |
Cust A |
BCD234 |
22 |
| 28/10/2022 |
Cust B |
CDE345 |
15 |
| 28/10/2022 |
Cust B |
EFG456 |
8 |
| 04/11/2022 |
Cust A |
ABC123 |
14 |
| 04/11/2022 |
Cust A |
BCD234 |
16 |
| 04/11/2022 |
Cust B |
CDE345 |
20 |
| 04/11/2022 |
Cust B |
EFG456 |
22 |
