How to filter out last 90 days of data in Query Editor

[vendersonalias0]

Spreadsheet A contains date (daily) and sales, however the sales are only available for the last 90 days. Since this is updated daily the days older than 90 days are 0 sales.

As a result we created spreadsheet B which is just a monthly archive of Spreadhseet A so that we can keep the sales column.

My workaround is to have the two queries appended, only taking the last 90 days from table A then excluding the last 90 days from Table B. Therefore there would be no overlapping dates.

I've tried some of the row filtering options but don't see any way to exclude relative date. Does anyone have a solution for the DAX required to transform the data accordingly?

Let me know if you have any other ideas. I was thinking I could merge the queries together then create new columns so that if table a sales  = 0, then table B sales. I wouuld go with this solution but I have more columns than in my example and I would also need to create unique identifier column

Thanks!

[BA_Pete · Accepted solution]

Hi @vendersonalias0 ,

It sounds like you might benefit from incremental refresh.

You can 'freeze' your existing data so it's not overwritten and only refresh the last X days data and add it to the existing static data. In your scenario, you would stary with your current appended history of data, then refresh from the new 90-day file every day, but set it to only refresh, say, the most recent 30 days (this would normally be set to the maximum number of days within which your facts can be edited).

Here's some more information about the feature and implementation: Incremental Refresh 

Pete

[vendersonalias0]

Got this up and running, and did some tests, it seems to be working appropriately despite using a flat excel file in sharepoint.

Just wondering about this statement though on the guide you linked :

Incremental refresh policies are defined in Power BI Desktop and applied when published to the Power BI service.

So this means that incremental refresh is only used in service, that means in my scenario I should refrain from refreshing that table in desktop right? Since it will call in old data with blank values then when I publish it, the data that isn't refreshed will be missing values

[BA_Pete]

@vendersonalias0 

That's basically it, yes. You also need to be mindful that once you deploy to the Service, you can't bring that report back via download due to the incrementally-built data size.

It's an incredibly powerful feature, but requires thinking through and testing VERY carefully before deployment to ensure that your report is in it's final state and can stand the test of time.

Pete

[vendersonalias0]

Amazing, this is exactly what I needed. Much simpler solution, thank you!