Forum Discussion
Filter dates as from previous ISO week (also valid when year change)
Hello,
I have the following data in a table visualization in Power BI. I am trying to filter the rows based on the Date column.
I am trying to filter all dates which are in the previous week and later.
The week is ISO, so it always starts on Monday and ends on Sunday.
The solution should also work for year change. For example, in ISO week number 1 for 2023, the filtered results should include the dates as from week number 52 in the previous year.
Is this possible? Any help is much appreciated!
| Date | Stock | Returns | Defective |
| 08/08/2022 | 3 | 4 | 8 |
| 01/01/2022 | 6 | 9 | 17 |
| 27/05/2022 | 2 | 5 | 10 |
| 20/10/2022 | 9 | 3 | 16 |
| 07/08/2022 | 5 | 6 | 15 |
| 12/08/2022 | 2 | 2 | 10 |
| 17/08/2022 | 7 | 8 | 22 |
| 20/08/2022 | 3 | 3 | 13 |
| 22/08/2022 | 6 | 3 | 18 |
| 29/08/2022 | 3 | 3 | 14 |
| 07/09/2022 | 2 | 7 | 17 |
| 16/09/2022 | 3 | 7 | 22 |
| 23/11/2022 | 7 | 5 | 20 |
| 11/12/2022 | 9 | 8 | 18 |
| 04/01/2023 | 6 | 7 | 16 |
| 17/02/2023 | 7 | 14 | 34 |
2 Comments
1 - Your date dimension only goes up to the current date so showing future records would be tough. Try extending it to the end of the current year or some other future date.
2 - Since you already have [CurWeekOffset], the following column should work for you.
Display = 'Calendar'[CurWeekOffset] >= -1I hope I understood you correctly.
5 Replies
ryan_mayuSuper User
Anonymous
is this what you want?
Column =VAR yearweek=year('Date'[Date])*100+WEEKNUM('Date'[Date],2)return yearweek<year(today())*100+WEEKNUM(today(),2)when you select true , you will get all the date before this week.Hi ryan_mayu Thank you for your reply!
Here are the results from your formula but it is not quite right:
We are currently in ISO week number 34, which started on Monday 22 August 2022.
I would like to filter all the dates as from the previous week, that is, ISO week number 33 which started on Monday 15 August 2022.
Here are the expected results (dates are sorted from oldest to newest):
Date Stock Returns Defective Filter 01/01/2022 6 9 17 FALSE 27/05/2022 2 5 10 FALSE 07/08/2022 5 6 15 FALSE 08/08/2022 3 4 8 FALSE 12/08/2022 2 2 10 FALSE 17/08/2022 7 8 22 TRUE 20/08/2022 3 3 13 TRUE 22/08/2022 6 3 18 TRUE 29/08/2022 3 3 14 TRUE 07/09/2022 2 7 17 TRUE 16/09/2022 3 7 22 TRUE 20/10/2022 9 3 16 TRUE 23/11/2022 7 5 20 TRUE 11/12/2022 9 8 18 TRUE 04/01/2023 6 7 16 TRUE 17/02/2023 7 14 34 TRUE - ryan_mayu
Anonymous
Updated the DAX.
pls see the attachment to see if this works for you