Period Over Period Functions with Date Controls

Hi all,

Anyone know if it’s possible to Period Over Period functions with a date control? My leadership team would like to see YOY data shown as YTD, but also wants the ability to change the dates so they can view Mar - May for example.

Every Period Over Period function I’ve tried to use does not allow me to filter different dates. I can properly calculate YOY, but as soon as I select a date then the YOY disappears or shows ‘No Data’.

In Power BI, the way I got around this was to use an ‘ALL()’ function, which essentially allows you to ignore all selected filters while performing the calculation. I have been trying to find something similar in Quicksight but haven’t had any luck.

Any assistance would be greatly appreciated.

Can see if this “DateHider” is what you are looking for?

Hi Zenia and Roy - I think that post above will help if you are building a line chart or bar chart where you want to show YoY of many data points at the same time. If you just want a single KPI but allow the user to filter to different date ranges, I would suggest using Parameter controls rather than Filter controls and then building all the YoY logic into your calculated field. Imagine you have two parameter date controls - start and end. Then your calc would look like this:

ifelse({order date}>=${start} and {order date}<=${end}, sales, 0) - ifelse({order date}>=addDateTime(-1, 'YYYY',${start}) and {order date}<=addDateTime(-1, 'YYYY',${end}), sales, 0)

You could break it down into 3 calcs instead (which would allow you to use the Current Period and Same Period Last Year values by themselves if you wanted, and then your 3rd calc just does the arithmetic using those to do either YoY or YoY %.

Current Period: ifelse({order date}>=${start} and {order date}<=${end}, sales, 0)

Same Period Last Year: ifelse({order date}>=addDateTime(-1, 'YYYY',${start}) and {order date}<=addDateTime(-1, 'YYYY',${end}), sales, 0)

YoY: sum({Current Period}) - sum(Same Period Last Year})

YoY %: sum({Current Period}) / sum(Same Period Last Year}) -1

Check out this working example: https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-YoY-using-parameters

Hi Jesse,

This is amazing - exactly what I was looking for! Great idea to use parameters instead of date controls. I am new to QuickSight, so trying to learn more about parameters and how they can be used.

I really appreciate your response, we have been stumped on this for quite a while.

Thank you so much!
Zenia

Thank you for sharing that link, Roy! I did try to use the “DateHider” solution, but unfortunately it seems to only work with a bar or line graph, and I was looking to use a KPI visual instead. This will definitely come in handy as I will be looking to graph the YOY values at some point.

I appreciate your response on this one!

1 Like