Data model design help - Adding rows to imported excel table and making new table persistent

[injeejay]

Hi,

I'm looking for advice on the direction to pursue for the following task (simplifed for ease of explanation).

I'm creating a table (let's call it CurrentMonthSummary) with DAX that groups and summarises data from a large main table (let's call it CurrentMonthDetails). This report will run every month as is implied. For Month 1 this is trivial. For Months 2+, the report will need to show differences between the current month and the previous month. So I either end up with a summary table called CurrentMonthSummary and table called PreviousMonthSummary, or just stick them in one table and use a column to distinguish between. 

The challenge I'm having is figuring out how to manage the transition of data from current month to the previous month, and make it persistent. I'm not sure whether to automagically turn the CurrentMonthSummary's data into the PreviousMonthSummary data using PowerBI (and refresh CurrentMonthSummary from the CurrentMonthDetail table), or to add a manual step to the monthly process that requires exporting data from last month's CurrentMonthSummary into an Excel sheet and using that as an data import for PreviousMonthSummary. 

Doing it using PowerBI feels wrong as its a data visualisation tool, and not meant for creating offline data stores but maybe I'm missing some good functionality it has to accomplish this. To add spice to the pot, this will probably turn into a requirement to store the summary data for all previous months for trending purposes...

Thanks in advance for your thoughts and opinions.

[v-xicai]

Hi @injeejay ,

I am not sure what desired result would you want, could you please share your sample data or desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

[danno]

Hi,@injeejay if you are working with a data warehouse then the table you store the data in would be a snapshot fact table, meaning it would store successive monthly snapshots in the same table, appended one after the other.
If you are working with extracts and excel/csv files in a filestore or share point/OneDrive you can employ a similar technique to pull in multiple files and append these files together. The sharepoint or file connector will allow you to bring in multiple similar files and merge them together in power bi via the PowerQuery query editor (Get Data). You can choose the range of files to bring in based on the file name or based on a date column in the source files (this would be slower as power query would have to read all files before filtering their content).
Once you have all the data in a table then you can use measures with time intelligence to compare figures for current month to previous months.
Hope this helps

[injeejay]

Hi Danno,

Allow me to clarify a bit further my issue. I've already imported and merged a range of tables (from Excel spreadsheets on a Sharepoint) to create a master table in PowerBI of all records for a particular month - let's say June. I'm then creating a new table in PowerBI, using a range of DAX expressions, that summarises values from the master table. So this will be my June summary table and I can make nice KPI visuals for reporting.

However, come July, I will refresh all the data in my model with new Excel spreadhseets and create a new master table, and this table will have no memory of data from June. I will also create the July summary table using the same DAX expressions as before.

My issue is I don't know how to go about comparing my July summary table to my June summary table. I know this sounds stupid, but where will my June summary exist? Before refreshing my data in July, can I get PowerBi to export it to an excel spreadsheet so that it persists, and I can then import it as "previous months" data?

If I can't do this, can I just keep adding each months summary data to the summary table, and add a column to say which month it is?

Ideally, I'd like to just have one table like below (heaviliy simplified but gives the idea), that each month adds in the summary data from the new month to all the previous months. At the moment, my plan is to manually export this table from PowerBi each month after it is updated, and import it for the next month as "previous month data" and add the new data to it - so if we looked at this table after the running the report in July, the July column would be populated.

[danno · Accepted solution]

Hi, if you want to keep history of data, you need to decide on how to store it.  I wouldn't go down the route of outputting copies of data from Power BI.  I would personally set up your file store to keep files for each month and then append them within Power BI in the query editor.  It will be less work in the long run for you, rather than exporting data out, and re-importing.  Just change the file name to include a date / month of export to ensure that the data exports aren't overwritten, and as mentioned use the sharepoint / file connector to append data in power bi - see this video for further assistance. https://www.youtube.com/watch?v=RAmSdxt863s  - just make sure that the files ideally keep the same format month on month.

[injeejay]

Hi Danno,

Thanks for that; I think we're nearly there and thanks for bearing with me. I keep copies of each month's detailed source data so it is possible to do as you recommened. I've thought about doing this myself. However, these are relatively large datasets, circa 150,000 rows per month and there is a lot of M and DAX ETL going on. It will be nearly 2 million rows after the first year and seems like overkill just to generate historical monthly summary tables that have a few hundred records in them. Pity there wasn't an easy way to automatically write these in-memory monthly summary tables to the data store each month so that can be imported again.

However, if this is the way to do it, then fair enough. I can limit it to a years worth of historical data in any case so the server resource requirements are manageable.

[danno]

Hi, a few million rows aren't an issue, you could call it overkill, but it does give you the advantage of detailed comparisons month on month and the ability to to more accurate predictive analytics based on your data (depends on what you need)  If you truly only want summarised data historically, then you can create your summary table using a query in an Excel file (the Get Data option in excel is the same as Power BI) - you could use that to summarise using Group By in the query editor, Save your summary files and keep them for a separate summary table which you would append files into your power bi report.   

[injeejay]

Understood, thanks Danno. I'll mark the appropriate reply as solution.