How to filter/import data for the last week from today's date

[Anonymous]

Hi All,

I have a need to basically filter data that has a date field called 'created_at'. Basically I need to somehow filter data from today's date-7 days with respect to the 'created_at' date field. Also, today's date would be dynamic in nature so for example if today's date is 3/11/2022 then upon filtering data, it should display from 3/4 till today for the table that has 'created_at' field. Come tomorrow, it would be from 3/5-3/12 so it should have the ability to change today's date dynamically.  Can someone please suggest a solution in this case, thanks in advance. 

[v-jingzhang · Accepted solution]

Hi @Anonymous 

If you only want to import last 7 days data into your model, @Anonymous 's solution will work. But this will not happen automatically. In Power BI Desktop, you need to click on Refresh button in Home ribbon to reimport the latest 7 days data. If you have published the report to Power BI Service, you should set a scheduled refresh for the dataset to refresh data every day. In this way, it will update the report to have latest 7 days data. 

You can also use the User Interface to filter rows based on date type column. Filter by values in a column - Power Query | Microsoft Docs

If you want to load all data into the model but filter a date column on a visual (or on a report page, or on the whole report), you can use the relative date filter in filter pane or use relative date slicer. Use a relative date slicer or filter in Power BI - Power BI | Microsoft Docs This would update automatically every time you open the report. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Hi @Anonymous 

You want to filter the date prior to load to model using Power Query, right? Filter the [created_at] >= Date.AddDays( Date.From(DateTime.LocalNow()),-7)