Calculating a 3 month average and YoY Growth

I'm trying to recreate some equations I made in excel in Alteryx.

I have a table with Revenue numbers by month. For each month, I'd like to calculate the 3-month average revenue (Average of current month + the last 2 months). See the attachment for an example and the below screenshot.

I take that 3 month average and the calculate a YoY growth rate (i.e. the growth of the January2020 3-month Average to the January2021 3-Month Average) - I would also like to recreate that equation in Alteryx.

So ultimately for each Month I would have the following metrics

Revenue

3 Month Revenue Average

YoY Trailing 3 Month Growth Rate

Any Ideas? Appreciate the help

YoY Trailing 3 Tableau Example.xlsx

Reporting

Tips and Tricks

Best Practices

Preparation

Accepted answers

Qiu

@spencd2
I think this is a very practical data preparation issue.
hope I understand your intention correctly.

0416-spencd2.yxmd

All comments

Qiu

@spencd2
I think this is a very practical data preparation issue.
hope I understand your intention correctly.

0416-spencd2.yxmd

DawnDuong

hi @spencd2

@Qiu solution is already elegant and clean.

Since you are probably a user of tableau as well (i see from the file name), I thought it is worthwhile to mention that the optimal data structure for this case is "narrow and thin" just like in Tableau.

Just like in Tableau where you need to transpose the data to have the "date" as a column before you can do all the table calculation, the first step in many Excel-based time series is to transpose the data so that you can have a "narrow and thin" datatable. Best practice for data involving date or datetime data is to format them to date/datetime by the DateTime tool or otherwise.

For Excel users, especially involving finance use cases where taking interval-based sum or average is commonly required, the Multe-Row tool is very handy AFTER the data has been transposed.
Cheers,
Dawn.

spencd2

Thanks @DawnDuong. I want to make sure I understand what you're saying by a "narrow and thin" data structure. I assume you mean rather than having individual columns for each date, you would have a "Date' field, with a line item for each time period (in this instance, MM/YYYY)?

"Best practice for data involving date or datetime data is to format them to date/datetime by the DateTime tool or otherwise."

Can you elaborate what you mean here? @Qiu solution looks great (thanks you!) and mirrors what I have in excel, but the data I'm actually working on is structured as I described (with "Date" as a field; the excel file is just a sample to disguise private data)

Any suggestions on how to structure @Qiu 's workflow to that format would be appreciated!

spencd2

Thank you so much @Qiu ! This looks to be exactly what I'm after.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703