Calculating/Graphing Rolling Averages

egold31

Hello Everyone,

My master data sheet has a column called "Date Opened" where the date is formatted as Day-Month-Year. I am creating a dashboard and want to create rolling averages. I would like it to count, the number of data points per day, month, and year. I also want to compute averages for these metrics. How would I go about creating a formula that can calculate a rolling average for the past 12 months? I want to display the current months count without having to manually change the data displayed in the dashboard.

The other issue is that I would like to graph this data. However, I am aware that when creating graphs on Smartsheet you have to choose a specific section of your sheet to pull data from. I am aware that you can pull data using the MONTH and YEAR functions. Are there any functions I can use to pull data for the CURRENT DAY, CURRENT MONTH, and LAST 12 MONTHS without referencing a specific month or year (such a 1/2/3 or 2024 in the formula)? My concern is that when graphing data for the past 12 months, I don't want to have to manually edit what it is graphing. I would like everything to auto update so that I dont have to create a new dashboard each year.

For instance if it is currently May, I want my Dashboard to provide a graph with Data from June of the previous year to May. I hope this makes sense. Any help is greatly appreciated.

Paul Newcome

Do you mean separate data points for each month across a chart or just a single number that shows the average for the past 12 months based on today's date?

egold31

@Paul Newcome

Ideally, I want to display 3 Metrics: the number of entries "open" for the current day, the past month, and the year. Using these metrics I could create rolling averages.

My hope with the graph was to be able to then graph the number of "open" entries for the past 12 months. The x-axis would be months, and the y-axis would be # of "open" entries.

Is there a way to do this?

egold31

@Paul Newcome

One more thing I forgot: I would like the dashboard to display the average daily "open" entries. Not sure how I would go about that.