Month-over-month and Year-over-year comparison in combination with date picker

Hi all! Almost all reports in my dashboard are using date pickers to allow users to dynamically select their reporting period. At the same time, users often want to compare metrics to the same period previous year or to the previous month.
As the date picker is filtering the dataset by dates, it is not straightforward to still display dates outside of the selected range (=month before, year before). Are there any solution that you would suggest on how to still getting a “Previous Month” or “Previous Year” column into a table?

Hello @bernhard.boehm !

Would adding calculated fields for each of these cases be possible for your table? Something like this:
Month: periodOverPeriodLastValue(sum({Sales}),{Order Date},YEAR,1)
Year: periodOverPeriodLastValue(sum({Sales}), {Order Date}, MONTH, 1)

Let me know if this was what you were looking for!

1 Like

Hello @bernhard.boehm !

I am going to mark my comment above as a solution for now, but if you still need help please feel free to reach out in the community!

Hi @duncan !
Apologies for never replying to your answer. Thank you for taking the time to help solving the problem. I still see two problems with applying periodOverPeriodLastValue but maybe I am doing something wrong: Let’s say I want to create a table that lists countries with a column that shows revenue from each country aggregated for the last 12 months. My goal is to have one extra column with a comparison to the previous year.

  • As far as I can see periodOverPeriodLastValue requires the date to be present in the table to avoid a “Table calculation attribute reference(s) are missing in fields wells” message. I could add the date as a hidden column and aggregate by year, but the moment my reporting period goes across years, it won’t work anymore.
  • The moment I apply a filter to the table that restricts the reporting period to the last 12 months, periodOverPeriodLastValue won’t return data for my Previous Year column that should display the period from 13 to 24 months ago.

Regards,
Bernhard