Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
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!!
Solved! Go to 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.
@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.
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.
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
16 | |
8 | |
8 | |
7 | |
5 |