Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Folks,
hope someone can help or advise. I have just noticed an issue with my use of "Last 12 Calendar Weeks" as a relative filter in visuals. A new set of data now has occurences on Sundays and they are being attributed to the wrong weeks. i.e. PBI is treating a Calendar week as starting Sunday and ending on Saturday. This wasnt obvious before when I created the report as there were little or no cases on Sundays. However I am no tracking additional material which does regualrly have Sunday activitiy.
I need to change it so that the Calendar week runs from Mon to Sunday.
Is this possible?
I have tried reviewing suggestions in the forum and cant find anything that resolves the issue.
Yes, WEEKNUM supports these:
Return_type |
Week begins on |
System |
---|---|---|
1 or omitted |
Sunday |
1 |
2 |
Monday |
1 |
11 |
Monday |
1 |
12 |
Tuesday |
1 |
13 |
Wednesday |
1 |
14 |
Thursday |
1 |
15 |
Friday |
1 |
16 |
Saturday |
1 |
17 |
Sunday |
1 |
21 |
Monday |
2 |
Greg, many thanks for swift response. However I am not sure how Weeknum helps me.
I am looking to display a graph with the X axis as the start date of a given week.
The data displayed is the number of calls handled in that week, shown as the sum of the calls for each day in that week.
I then want to set the graph to only show the data for the last 26 calendar weeks, including weeks from previous years if the data overlaps year end. How would I use Weeknum to achieve this?
The problem is that although the X axis shows the start date as a Monday (created using Start of Week function in Power Query), when I set the filter on the visual to a Relative filter of Last 26 Calendar weeks, it uses a week as starting on a Sunday not a Monday. As such the Sunday data shows in the wrong weeks.
Hi @carlbh ,
Create a column to combine year and week.
year_weeknum = var weeknum = WEEKNUM('Table 2'[Date],2)
var Y_W = weeknum+YEAR('Table 2'[Date])*100
RETURN Y_W
Rank the year_weeknum.
week = RANKX('Table 2','Table 2'[year_weeknum],,ASC,Dense)
Create a measure to apply to the visual level filter.
Measure 2 = VAR MAX_WEEK = CALCULATE(MAX('Table 2'[week]),ALL('Table 2'))
RETURN IF(MAX('Table 2'[week])<MAX_WEEK&&MAX('Table 2'[week])>=MAX_WEEK-27,1,0)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
89 | |
82 | |
76 | |
64 |
User | Count |
---|---|
142 | |
111 | |
108 | |
99 | |
95 |