Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
vendersonalias0
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

@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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.