Attempting to create Historical Data Table

[Bryer]

Currently I have a table in power BI that pulls in from a SQL server, it only pulls the last 30 days of data because that is all that is stored on the server. I want to be able to build a simplie historical data table in power BI that stores data from further back however, by this I mean it will start storing the data now and continue to build on itself in the future. I want the historical table to just be a simple measure from the source table that counts the amount entries by month, so it would just be two columns one for the month and one for the count. So the goal is for the historical table to grow for every month so I can build a trendline, while the table being pulled from SQL will always only be pulling the last 30 days of data. Is this possible to do? And if so how?

[MNedix · Accepted solution]

Look for appending queries. You can either 1. generate daily data to be stored on an OneDrive or Sharepoint, then import it in PowerBI, then append the new query to the existing one or 2. via Direct Query perhaps (never done it though).

[danextian · Accepted solution]

hi @Bryer 

The data being fed into Power BI is the data from the source. If your data doesn't have the records from beyond 30 days then Power BI won't have it as well.  That being said, you must build a source that has the data you need. Alternatively, if you're in a Fabric SKU you can use dataflow Gen2 that allows data to be appended to the result of the previous runs  instead of replacing them but for your use case and for a very simple table, I would just build that historical source data.

[MNedix · Accepted solution]

Look for appending queries. You can either 1. generate daily data to be stored on an OneDrive or Sharepoint, then import it in PowerBI, then append the new query to the existing one or 2. via Direct Query perhaps (never done it though).