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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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

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. 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @Anonymous 

 

If you only want to import last 7 days data into your model, @Vera_33 '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

vjingzhang_0-1647338259875.png

 

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. 

vjingzhang_1-1647338565808.png

 

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

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @Anonymous 

 

If you only want to import last 7 days data into your model, @Vera_33 '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

vjingzhang_0-1647338259875.png

 

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. 

vjingzhang_1-1647338565808.png

 

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

Vera_33
Resident Rockstar
Resident Rockstar

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)

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors