Running 52 weeks - create filter for repport

[bilingual]

Hi, i am working on a report which is updated weekly, and which needs to run the last 52 weeks, can anybody help? 🙂

Kind regards Daniel

[visheshjain]

Hi @bilingual ,

You can hide the slicer from the visibility pane.

Thank you,

Vishesh Jain

[v-stephen-msft · Accepted solution]

Hi @bilingual ,

So you want to filter the data for the last 52 weeks, right?

If you have a date column in your data source, then you can use a relative date slicer to filter:

Use a relative date slicer or filter in Power BI - Power BI | Microsoft Docs

There is a difference between Weeks and Weeks (Calendar), it can be found in the above document, which has an explanation.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bilingual]

Thanks a lot Stephen, it seem very intuitive. At  present I only export PowerBI as PDF,so i need the slicer to act as filter for all pages without being visible, aka as a normal filter.

What would be the optimal solution for doing so?

[visheshjain]

Hi @bilingual ,

Could you please provide more information as to do you want to update data of the last 52 weeks only or do you want calculations of the last 52 weeks and does your data contain only the last 52 weeks?

You can try the following link as to how to set up your calendar table for calculations over weeks:

Week-related calculations – DAX Patterns

Lastly, as @mhossain suggested that you can look into incremental refreshes but that won't work with the free license.

Hope this helps.

Thank you,

Vishesh Jain

[bilingual]

Hi , i periodically update the data every week by Excel files which has data from 2018, so when a new week is ready, i need a filter in the report which for example can be used to only show data greater than or equal to Week 1 2021 this week.

[visheshjain]

Hi @bilingual ,

I would suggest, that you create a YearWeek column in your calendar table, which will be numeric and then use that as a slicer on your report page.

You can create the slicer column based on the link I shared.

So the column will be 202101 for 2021 week 1, 202102 for 2022 week 2 etc. and the put this column as a slicer on the report page.

You can paste the same slicer on every page of your report, use sync slicers and hide it from the visibility pane from every page apart from the 1st page of your report. This way you have to make the selection only once.

Hope this helps.

Thank you,

Vishesh Jain

[mhossain]

@bilingual 

If I read it correctly you require to keep only last 52 weeks of data in your model, what is your source, is it the relational database? Please check incremental refresh:

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

If you are unable to use incremental refresh, you can apply filter.

Please elaborate more if above doesn't solve.