Forum Discussion
Refresh once a month and keep old data
Hello,
I have a financial dataset that updates once a month. It’s on a SQL Server, and I execute a SQL query of all data everytime I refresh my powerbi.
Like
SELECT * FROM FinancialTable Where Date >= ‘2018-01-01’
Is there any way that I dynamically filter the SQL query to get only the data from the last month, but keep all my historical data? I can’t use the ‘incremental load’ because I don’t have PowerBI Premium.
Regards.
I have a financial dataset that updates once a month. It’s on a SQL Server, and I execute a SQL query of all data everytime I refresh my powerbi.
Like
SELECT * FROM FinancialTable Where Date >= ‘2018-01-01’
Is there any way that I dynamically filter the SQL query to get only the data from the last month, but keep all my historical data? I can’t use the ‘incremental load’ because I don’t have PowerBI Premium.
Regards.
3 Replies
- Greg_Deckler
Community Champion
Well, you could do this with somewhat of a manual approach. In essense, 3 queries. First query gets everything <= some date. Second one gets everything > the same date. You Append the two queries together. You disable refresh on the first query. Now you have significantly reduced the amount of data that you are refreshing. You can later go in and adjust the dates in the queries and enable refresh on the first query. This will result in a large data load, then you disable the refresh on the query again.
It's manual, but it works without Premium.
- Hello Greg, thank you for the reply. This could be a solution, but my monthly data can’t be changed, so I would have to do this manual approach every month.
I’m thinking to make the monthly load using sql server agent by inserting the data on a new table, but I would like to have a solution just using Power bi.
v-danhe-msftMicrosoft Employee
Hi Guilhermealc,
Based on my research, you could refer to below blog.
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/
Regards,
Daniel He
