Year over Year Calculation with Dimensions

I’m trying to just do a year over year period calculation with a date hider as shown in several of the how-to and community documentations such as here: Creating period comparisons with QuickSight's periodOver and periodTo functions
and here: Top 12 Months Sales with Previous Year Sales in Table Visual
However when I add a dimension to the table such as “Category”, it does not show the data for the previous years if there are no entries for the current selected year. Is there any way to fix this so that category E for example would show a value for Sales Prior Year if Sales in the current year = 0?
I understand this as a limitation due to the date hider.
Thank you

Hi @analytics1,

Welcome to the QuickSight community!

Based on the description of the issue it appears that the date field in your dataset is coming from your Fact/Transaction table. So, it is possible data for a category may be available in a specific time period and not in others.

Generally, this is handled by having the Date as a separate dimension table where you have dates for all your reporting periods. You then do a left join of the date table with the Fact table on the date field ( Date is on the left side of the join ). This way whether you have transaction for a particular period in your Fact table or not you still have the date value. Similarly you will have to have your Category as a dimension table and do a left join with your Fact table.

Please note that the dataset size will grow since for the Date & Category combinations where there is no data in the Fact table will show blank values for the Fact table columns but the date and category will be available in the dataset.

Hi, my data is already left joined on the dates. I don’t see this is a valid solution because I don’t want every category to show all the time. Only when there’s a value in the previous year or current year. But the date hider filter effectively removes the connected data prior to the current year. Thank you.

Hi @analytics1,
It’s been awhile since last communication took place on this thread. Were you able to find a work around solution for your case or did you have any additional questions?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

I haven’t found an answer to this.

Hi @analytics1

How about handling Sales, Previous Sales, Variance as calculated fields with the date filter as part of the calculated field?

You can not include the Date field in the pivot but the calculated fields have the relevant values as you have filtered data as per the selection in the parameter.

In this case the calculated fields will show up in the pivot for the all the categories you have in your dataset. See if that works for you.

Regards,
Giri