cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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.

 

NameDate SubmittedWeek NumberScore
Max Gilbert11/10/20214158
Jadyn Levine21/10/20214238
Trenton Espinoza29/10/20214343
Anahi Johns08/11/20214562
Katherine Waters12/11/20214530
Max Gilbert20/11/20214678
Trenton Espinoza28/11/20214745
Jazlynn Mcclure02/12/20214888
Anahi Johns10/12/20214959
Everett Lang15/12/20215078
Anahi Johns16/12/20215092

 

Any help would be much appreciated!!

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

View solution in original post

3 REPLIES 3
AlexisOlson
Super User
Super User

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

 

AlexisOlson_0-1639758014021.png

Anonymous
Not applicable

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

MakeItReal_0-1639760514222.png

 

 

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.

Helpful resources

Announcements
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors
Top Kudoed Authors