Create copy of Smartsheet data, scheduled and automated to be used as trendlines

B Call

I have created a PowerBI dashboard using smartsheets as the data source. Live updates are working perfectly, and updating data is running smoothly.

I have been asked to show a trend line, and progress with these metrics. How can I build a table (or Smartsheet) using snapshot data in an automated workflow using smartsheet?

Kerry St. Thomas

You could leverage the idea of a KPI sheet and some automations as an intermediary between the "raw" data in Smartsheet and PBI. Basically, create a sheet that does your aggregation calculations for what you want to show as a trend. Then set up a Copy Row automation to push those calculations to another sheet that'll be your datasource for the historical data- and on that new sheet, include a Created column to capture the datestamp when the data are copied.

This of course presupposes that you won't need full drill-in capability for this historical trendline visualizations - if you do need full drill-in, this will get unwieldy quite fast.

Kerry St. Thomas

You could leverage the idea of a KPI sheet and some automations as an intermediary between the "raw" data in Smartsheet and PBI. Basically, create a sheet that does your aggregation calculations for what you want to show as a trend. Then set up a Copy Row automation to push those calculations to another sheet that'll be your datasource for the historical data- and on that new sheet, include a Created column to capture the datestamp when the data are copied.

This of course presupposes that you won't need full drill-in capability for this historical trendline visualizations - if you do need full drill-in, this will get unwieldy quite fast.

spindle

Thanks for your reply, it was very helpful!