I am trying to show three columns for total sales in last three months separately with month label .
Need to calculate below aspects
- Average sales for last three months
- Average minus last month sale
ifelse({date_dt}>=addDateTime(-3,'MM',now()),value,0)
This would be a custom measure something like “Last 90 days value” and date_dt would = the date from your data set.
The function reads like "If your date is greater than or equal to 3 months before today, then use “Value”, else 0. You can then use that as a sum, or an average, and use the date_dt on the x axes, aggregated by month.
You would then have a second custom measure that does this same thing for only the past 30 days.
Hi @ZainIshtiaq, FYI if your account is subscribed to Quicksight Q then you have the ability to try to generate calculations like this using language. Generative BI dashboard authoring capabilities now available in preview for Amazon QuickSight Q customers | AWS Business Intelligence Blog
Aside from that I think you will want to use our level-aware functions. Using level-aware calculations in Amazon QuickSight - Amazon QuickSight
- windowAvg(sum({sales}), [truncDate(‘MM’, {date}) DESC], 0, 2)
- {field from number 1} - periodOverPeriodLastValue(sum({sales}), {date}, ‘MONTH’)
Thanks @oanielsen for a quick reply. Let me try it
Hey @crease . Thanks for the input. Will try the mention points to cater my requirements.
Hello @ZainIshtiaq, welcome to the Quicksight community !
Was @oanielsen or @crease 's suggestions helpful, and if so could you mark their post a solution to help the community?
It has been a few days since we have heard from you but would still like to help you find a solution!