Period over Period difference - a way to choose the period?

Hi everyone,

The periodOverPeriodPercentDifference function almost accomplishes what I want to do:

The problem is, especially for Year over Year comparisons, the current period is often biased because it’s not finished so it has a lower count which makes it seem like this year/month we’re underperforming.

Is there a way to get the same calculation but where the months are 8th June to 8th July, then 8th July to 8th August etc… instead to eliminate this bias? (which would change to the 9th if you’re looking at the dashboard on the 9th day of the month)

Thank you!

If you can do calculations in SQL, I would definitely recommend it because there are a couple of issues with displaying the data if using only QuickSight calculated fields.

However, here is how you can do it in QuickSight.

First, if you want to do it from today, you would need to get the number of days from the start of the month/year.

days from start of month = dateDiff(truncDate(‘MM’,now()),truncDate(‘DD’,now()),‘DD’)

Then add that amount of days to the timestamp you are using so that it will group days into one month based on the number of days into the month.

group dates to days from current month =

Note: you need to subtract an extra 1 from days to account for today.

Next you can make your periodOverperiodPercentDifference calculation and use the newly created calculated field as the date time.

periodOverPeriodPercentDifference(count({session_id}),{group dates to days from current month},MONTH,1)

This was it previously.

This is it now with the newly created fields and calculations.

The reason I suggest you doing this in SQL is because you need to reference the original timestamp if you want to display addDateTime with a field. This can get confusing as you can see, because sometimes there are overlapping instances such as the original event timestamp being in 2022 and 2021 and the new group only being in 2021.

If you do it in SQL you don’t have to reference the original field so you can just refence the newly created field.

Let me know if that solves your issue!

Hi, @Rachel. We hope that Max’s solution worked for you. I am marking his reply as, “Solution,” but let us know if this is not resolved. I’ll also reach out to the QuickSight Team to share your feedback. Thanks!