Dynamically Reference Values for the Previous Year based on User Filter Selection

JT_DC_2023

Hi everyone,

I am currently trying to implement a dynamic filter for a KPI card in Oracle Fusion Data Intelligence.

In the below image, I have laid out an example:

• We have two KPI cards:
  • “Sickness Rate (%)” – reflects the selected year from a Year filter (working as intended)
  • “Sickness Rate Previous Year (%)” – should automatically show the previous year based on the selected year (not dynamic)
• Additionally, there is a line graph that plots the sickness rate across the selected and past three years, which works fine (for visualization purposes)

The issue:
While the “Sickness Rate (%)” updates dynamically based on the user’s year selection, the “Previous Year” KPI only works with a hardcoded year. I would like this value to automatically reflect the year before the one selected in the year filter.

Has anyone implemented a dynamic solution for a similar use case?
Can I reference the selected year and subtract 1 dynamically within the visual or filter expression?

Any ideas, best practices, or workarounds are greatly appreciated!

Thanks in advance!