DAX measure to filter dates only for this week starting Monday

[Anonymous]

Hello,

I have the following table named 'Student Records' loaded in Power BI from an Excel sheet which auto-calculates the week number from the Date column using ISOWEEKNUM formula.

Date	Week Number	Student Name	Country
01/12/2021	48	Lizui	China
06/12/2021	49	Laufenburg	Germany
16/12/2021	50	Tegalpapak	Indonesia
19/12/2021	50	Ar Rabiyah	Kuwait
21/12/2021	51	Bellegarde	France
23/12/2022	51	Gangarampur	India
03/12/2021	48	Luntas	Indonesia
11/12/2021	49	Frei Paulo	Brazil
13/01/2022	2	Seedorf	Germany
14/02/2022	7	Cosamaloapan de Carpio	Mexico
21/03/2022	12	Zagrodno	Poland

I am trying to write a DAX measure that i could add as a filter for this figure in Power BI so that it only shows the dates that are in the current week starting on Monday until Sunday. The relative time filter for this week in Power BI only works as from Sunday until Saturday.

I am new to Power BI and so far this is what i have and it should be working in my opinion. But for some reason, it cannot find the table column [Week Number] when i type in the formula and it gives me an error:

Filter This Week = IF(WEEKNUM(NOW(),21) = 'Student Records'[Week Number],1,0)

Any help would be much appreciated!!

[Vera_33 · Accepted solution]

Hi @Anonymous 

I guess you are using the measure in Filters on the visual? There is row context in your original one, so it works in DAX Calculated column, not measure, you can do it like this

Filter This Week = IF(WEEKNUM(NOW(),21) = MAX('Student Records'[Week Number]),1,0)

[Anonymous]

@Vera_33 Is it possible to modify the measure you suggested to filter all dates since the previous week?

I tried the below but it does not work. 

Filter Last Week = IF(WEEKNUM(NOW(),21)-1 = MAX('Form SLS Data'[Reporting Week Number])-1,1,0)

[Vera_33]

Hi @Anonymous 

You mean last week and this week? There are 2 problems:

what if it is the first week as of today?

what if you have more than 1 year data?

filter weeks = 
VAR CurWeek = WEEKNUM(TODAY(),21)
RETURN
IF(MAX([Week Number])IN {CurWeek, CurWeek-1},1,0)