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 =
addDateTime(-dateDiff(truncDate(‘MM’,now()),truncDate(‘DD’,now()),‘DD’)-1,‘DD’,{event_timestamp})
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!
