Best way to compile rolling metrics for Dashboard Update

kss5229

I'm working to build a dashboard that presents monthly metrics. I want the dashboard to automatically update to display the last 12 months' metrics. I have a metrics spreadsheet that calculates all the information for each month.

I'm wondering if the best way to achieve this is to create a new smartsheet with a formula to calculate & populate the last 12 months and then using index/match (or some variation of that) to pull data from the original metrics spreadsheet or instead to create a report that pulls data from the metrics smartsheet, is filtered by date within the last 365 days (or something like that).

What would the pros and cons be of each? Is there a better option I'm overlooking?

RAdamowicz

If I'm following correctly, I think the easiest method with the least chance of it breaking would be to create a report from your metrics sheet with a filter that removes the older metrics. That can then be added as a report or a chart to your dashboard, depending on how you want the data visualized. I would filter by a date column (you may need to make a helper column in your metrics sheet that is either the first or last of the month you're recording for, i.e. 12/01/2024 if you don't already have a date) and then select [Date Column] "is not in the last (days)" and put 365 in so you just get the last year. OR if you have a column that identifies year, you cold also filter by that and choose [Year Column] is equal to 2024 or however you have it formatted. Hope that helps!

Jennifer Kurtz

Hi there!

I think creating a report that shows your data within the last 365 would be the simplest solution.

You could consider, depending on how you'd like to organize your data, creating a chart/graph. Visual representations can communicate data in powerful ways - and they're often scan and digest. :)

kss5229

Thanks for these responses! Yes, I plan to primarily use charts on the dashboard to visualize the data. I wasn't sure if using a report rather than a normal sheet might limit my ability to use different graphic visualizations or if there were nuances between the two like the data auto updating (e.g. I believe formulas that use the TODAY() function require the user to view the sheet daily for the data to update).

Right now it looks like a report is the cleanest way to go. Thanks!