Quicksight KPI on last month spending vs average three months spending

Question

![CUDOs billing summary page](/media/postImages/original/IMZqBnKreoS3WdjX3Pd2zJAQ)
Hi,

Thank you for making the CUDOs template. I have a question about the KPI table. Is it possible to get the last three months average spend vs last month total spend using the KPI table?
For example, in the screenshot took from the public template dashboard, I would like to get (Nov 2022 + Dec 2022 + Jan 2023)/3 vs Feb 2023, which would be (1.04M + 0.93763M + 0.75624M)/3 vs 0.6443M

Thank you!

Answer

My attempt:
1. monthTruncDate: truncdate("MM",{billing_period})
2. LastMonthSpending: ifelse(dateDiff(monthTruncDate,maxOver(monthTruncDate,[],PRE_AGG), 'MM') = 1, {unblended_cost}, 0)
3. LastThreeMonthSpendingAverage: 
ifelse(
    dateDiff(monthTruncDate, maxOver(monthTruncDate,[],PRE_AGG), 'MM') = 2, {unblended_cost},
    dateDiff(monthTruncDate, maxOver(monthTruncDate,[],PRE_AGG), 'MM') = 3, {unblended_cost},
    dateDiff(monthTruncDate, maxOver(monthTruncDate,[],PRE_AGG), 'MM') = 4, {unblended_cost}, 0)/3

I checked against the cost explorer and I think LastMonthSpending value is correct but LastThreeMonthSpendingAverage is slightly off by about 0.7 dollar. Can someone help me check if my approach is correct and scalable? Thank you.

Quicksight KPI on last month spending vs average three months spending

Relevanter Inhalt