Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. 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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
13 | |
13 | |
12 | |
11 |