KPI metric values

Hello Team,

I have monthly metric data from Jan’21 to Dec’22 as attached below and I am trying to calculate the YoY% for the ‘current_mau’ and ‘first_usage’ field values using the formula periodOverPeriodPercentDifference(sum({monthly_first_usage}) , FormattedDate , YEAR , 1)

Upon setting up the calculate field, it is giving me blank values as shown. Can anyone suggest what is wrong or an alternate approach in computing this?

Hi Aakashsp,

Please see if the following solution from Jesse on a similar post can be adapted for your use case:

Last 12 months: ifelse(dateDiff({order_date},now(),"MM") <= 12, sales, 0)
Last 13-24 months: ifelse(dateDiff({order_date},now(),"MM") <= 24 AND dateDiff({order_date},now(),"MM") >12, sales, 0)
YoY %: sum(Last 12 Months) / sum(Last 13-24 months) - 1

Similar post: How to calculate YOY % through certain date?
Learning Center: Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc)

If the above does not work, please send me an email with your sample data and calculation in MS Excel. Include a sheet with your calculated fields/formulas in QS.

Sincerely,

Alex
drpeter@amazon.com

1 Like

Thank you for the suggestion! I tried using the formula Monthly YoY % Diff:

`(sum({This Month}) - sum({This Month Last Year})) / sum({This Month Last Year}) and also the way you suggested but it still gave me a blank value. I might be missing something but I’ll email you my sample data and the calculations I used. Thanks again!

Hi Aakash,

Try the below approaches –

https://repost.aws/questions/QUUcFFmUcDR3WPYjBfFXtQqA/yo-y-and-mo-m-metrics-up-to-certain-day-of-year-or-month-in-quick-sight

Sincerely,

Alex