This sounds like a relatively easy request, but the challenge is I'm showing a bunch of metrics in a single table against a target. Both Current Year (CY) and Prior Year (PY) are required by the business as rows.
The table would look like the below ideally:
Orders
2023 Dec
Current Year
150
Target
125
Prior Year
115
We use a canonical type calendar in our apps as many of our objects use dates from over 10 types (lead, campaign, order, revenue recognition, AR, etc) so we can use a common date axis with many KPIs.
Long story short, how would I get Prior year to show 2022 Dec #s on a table with 2023 Dec as the Month of the calendar? If it helps, in our set expressions we use a field created in the calendar called CurrentMonthID. Current month is 0, 12 months ago is -12, etc. Makes it simple for flagging and date ranges with fiscal calendars.
