Hey,
I've been trying to do this for ages now, and I either have missed something very simple or what I'm trying to do isn't plausible.
I'll try include as much detail as possible.
At present, I have a product table:
| Product ID | Original Season | Previous Season |
| 0 | AW21 | AW20 |
| 1 | AW22 | AW21 |
| 2 | Cont | Cont |
| 3 | SS22 | SS21 |
| 4 | SS23 | SS22 |
My product table is joined to my raw data table by the product ID.
At present when trying to show this data vs. last year, I get the following (as expected):
| Original Season | TY | LY |
| AW21 | £5,000 | £200,000 |
| AW22 | £100,000 | £0 |
| Cont | £50,000 | £25,000 |
| SS22 | £5,000 | £125,000 |
| SS23 | £120,000 | £0 |
Now, my desired outcome, and the thing I haven't cracked:
| Original Season | TY | LY |
| AW21 | £5,000 | - |
| AW22 | £100,000 | £200,000 (value of AW21 LY) |
| Cont | £50,000 | £25,000 |
| SS22 | £5,000 | - |
| SS23 | £120,000 | £125,000 (value of SS22 LY) |
As you can see, for TY I'm wanting to use the 'Original Season', but when doing the LY calculation I want it to look at the 'Previous Season' instead, which as seen from Product Key is in the same table.
I've tried combinations of TREATAS/VALUES, and disconnected tables but I can't quite it perfect.
In another file, I pull the LY separately and then just overwrite to the value I want it to sit on (e.g. I would overwrite AW21 in the LY data to AW22, so when looking at AW22 TY and LY it appears in both sides), but I'm trying to do this in my main dataset which is product level.
