How to create page-filter based on max value of column in PowerBI?

Question

I have a column called "Week", which always will take two values; the number of the week of the year we are currently in, and the number of the week of the year that just completed. E.g., 10 and 9. This report will be refreshed daily, thus the values of the Week column are dynamic.

I want to have a page-wide filter that looks at only the current (or previous) week, and will NOT require me to manually select the filter at the start of every new week.

What I have tried:

• I can create a calculated column that assigns the current week a value of 1, and previous week a value of 0, then I am able to apply a page filter, since this is now static. However, I am trying to reduce the size of my data, so I want to refrain from creating a column.
• I have tried creating a measure instead of a column but PowerBI does not allow for measures to be used in page wide filters.

Are there any workarounds for the issue I am having?

Answer 1

Regarding your first bullet - a single column with a small integer will have almost zero impact on your model size. It is also not static in the sense you mean as it will update at refresh time when new data is ingested and the week number changes. There is no issue with this solution that I can see.

The only additional advice I would give is to add your custom column to a dedicated date table which will have a relationship to your fact table. Presumably your fact table contains dates already and the additional column in your date dimension will be even more negligible in terms of size.