cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Guilhermealc
Regular Visitor

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.
3 REPLIES 3
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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.

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!