Filter last 4 weeks based on today date, including year change

[Anonymous]

Hello,

I have the following table named 'Form Data' which is updated on a daily basis and the 'Week Number' is a calculated column using the ISOWEEKNUM formula in Excel. I imported this table into Power BI and i want to always display the last 4 weeks in a graph, and that includes next year. The problem is when the year changes from 2021 to 2022, the week number will go from 52 to 1.

I am trying to write a DAX measure that i could place into the filter for this visual in Power BI, so that it can automatically filter the last 4 weeks dynamically based on the current date and also take into account the year change.

Name	Date Submitted	Week Number	Score
Max Gilbert	11/10/2021	41	58
Jadyn Levine	21/10/2021	42	38
Trenton Espinoza	29/10/2021	43	43
Anahi Johns	08/11/2021	45	62
Katherine Waters	12/11/2021	45	30
Max Gilbert	20/11/2021	46	78
Trenton Espinoza	28/11/2021	47	45
Jazlynn Mcclure	02/12/2021	48	88
Anahi Johns	10/12/2021	49	59
Everett Lang	15/12/2021	50	78
Anahi Johns	16/12/2021	50	92

Any help would be much appreciated!!

[AlexisOlson]

The first thing I'd try is relative date filtering.

[Anonymous]

@AlexisOlson Thanks for your suggestion. But i have already tried it and i encountered this problem where the week start day is Sunday and it ends on Saturday. I want it to be ISO week number, starting with Monday and ending on Sunday. 

Below is a screenshot from Power BI to illustrate the problem. The relative date range for last week (number 49) should be 6/12 to 12/12.

[AlexisOlson · Accepted solution]

Yeah, the relative date filtering is limited but I thought it was worth a shot. It sounds like you'll have to use your date table and a measure.

I'd recommend creating a relative week index on your date table as suggested here:
https://www.oraylis.de/blog/2016/how-to-create-relative-week-column-in-power-bi

RelWeekIndex =
VAR StartOfWeek = DimDate[Date] - WEEKDAY ( DimDate[Date], 2 ) + 1
VAR StartOfCurrentWeek = TODAY () - WEEKDAY ( TODAY (), 2 ) + 1
RETURN
    ( StartOfWeek - StartOfCurrentWeek ) / 7

You can then add this column to your filters and pick -4, -3, -2, -1 or whatever weeks you'd like.