How can i take a monthly backup of my table and insert it into my table?

[pierreali]

My goal is to be able to mesure the usage of my servers and the increase in usage.This is an example of my table.

How can i store the values of total_size in this moment in another column to be able to calculate the increase later on?

[Seward12533]

I don't think you can do this in Power BI.  You may be able to do it with MS Flow or SSIS.  There may be hack in PowerQuery where you can append the curret data to an archive after adding a timestamp.  If you set it up for Daily refresh it would append it but there might be recursive issues.

You could manually do it by duplicating your data into another table and then turning off the inclide in refresh and then compare agains the baseline or set up incremental refresh on that table to update montly (if your a PowerBI Premium user.

[Anonymous]

Hello Seward,

i like your idea of undoing the struggle with a monthly backup by creating a data archive with timestamps. Therefore could you give me some guidance or tips on how to realize this in the easiest way?

[Seward12533]

Could not figure out how to do this in PowerBI and my company does not have FLOW installed. So here is my hack using Excel with Macros. 

• Open PowerQuery (Edit Data) in PowerBI and copy the query for your data file
• Paste into PowerQuery in Excel and have it Craete a TABLE in the workbook.
• Add Column to your data to caputure the current Date/Time using NOW()
• Create sheet and copy results as values, convert to table and format as desired
• run Macro to Refresh the data, fill the calculated dates back in where they are blank for any new records, then copy table and append to the bottom of the archive.  NOTE you must disable REFRESH in BACKGROUND for your query connections in excel for this work or it will copy the data before its been refreshed.

You can now save this sharepoint, network or whereever and schedule a refresh. (I will use save it to sharepoitn and use PowerUpdate to schedule regular refreshed but you could configure the workbook to run the marcro when the spreadsheet is opened and add to the macro to Save the File and close the current workbook and then use any number of solutions to schedule the   file to be opened on a schedule. You can then link to the archive table from the excel workbook in your powerbi model and use DAX/Slicers/etc to compare current to most recent or any previous date.

https://filedrop.telusa.com/message/OlrmApqEYaaVhXVPY7pkJ4

Here is a simple workbook that grabs the contents of directory. If you want to see it in action - you will need to edit the query to look at a folder on your local machine.