Hi, relatively new to Quicksight after using GDS before to build dashboards for video production stats. I’m looking at video submissions, number of amendments etc over time and generally compare weeks/7 days or months/30 days using a pinned filter. Along with bar charts I want to show the headline numbers with the variation versus the previous period. Using KPI and trends the problem I see is that I can’t use rolling 7 days or 30 days as the comparison? If I do it by week (for example) today, it will compare last week to this week so far, as opposed to a rolling 7 day?
Is the only way to do it to create new calculated fields for each metric and then use them for the KPIs with trends? Something like;
ifelse(dateDiff({order_date},now()) <=30, sales,0)