Filter dates as from previous ISO week (also valid when year change)

[Anonymous]

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

[ryan_mayu]

@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.

[Anonymous]

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

[Anonymous]

@ryan_mayu The table is showing all values from beginning of 2022 to end of 2023. Instead, it should only show the expected results as in my previous post.

I have uploaded a new PBIX file with a calendar and i added the ISOYearWeekNum column to it. I also imported the original table. Is it possible to filter the Date column and get the expected results (without any additional rows)?

Download link from my Google Drive: https://drive.google.com/file/d/1C9anYhragY_6Z8ArHU7c9mxwDkWBn_ku/view?usp=sharing

Expected results:

[grantsamborn · Accepted solution]

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] >= -1

I hope I understood you correctly.