Snapshot Dynamic Data

I made a sales reporting dashboard based on SugarCRM which works quiet well. The only thing I am struggling with is to automatically make a snapshot of the salesfunnel (measures) every last day of the month an keep that unchanged. Is there a way to do this in powerbi. Currently I manually write down the numbers every last day of the month, put them in an excel which is then used as input for my report. I personally find this a kind of primitive but could solve it so far. Any suggestions are very much appreciated.

Just so I understand this correctly, are you looking to get a snapshot of what the data look likes historically every single end of month?

This is quite difficult for Power BI as it’s not really set up for this. You would usually do this say at the database layer and then feed a table into Power BI that has all the data.

Here’s a Power BI community post which explains a few options - https://community.powerbi.com/t5/Desktop/Data-Snapshots/td-p/354840

None of these really feel much better than what you are doing now, but just other options to review.

Maybe you could also setup a table in a specific format and just export that at the end of every month. Similar to what you are doing now, but saves you have to write it done. You can obviously place your data into many different formats in Power BI and export it however you like.

Sam

Hi Sam - Does using the incremental load feature of Power Bi solve this issue? That is, the issue of being able to save “snapshots” of historical data so they don’t get overwritten on refresh. My database guy swears it does, but other experts I’ve asked in various forums say it does not.

We have the same use case at our company. We need to preserve historical snapshots of future forecasts down to the SKU level (who is buying what from whom and when). We have the typical monthly update process, so we need to preserve our monthly view of the future as it updates so we can see what incremental intelligence has come in.

If the data set is nice and small you can just export the table view into a CSV as @sam.mckay described and label it as something (January sales funnel or similar). Then you could append all your views together back in the model and do time studies. Beyond a certain size, though, the native export function doesn’t work. Our data set as I described above is 3M records every month, so we’ve used DAX Studio to export it. Just connect it to the pbix file, enter EVALUATE (table name) and select the format you need in the Export options.

1 Like

Hi Experts, I would like to check if we have a better solution than manually exporting data each time? Say, saving the output in a virtual table each month and using it as an input for the tread analysis?