Set Calendar week in Relative filter to start on Monday and end Sunday

[carlbh]

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.

[Greg_Deckler]

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

[carlbh]

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.

[V-lianl-msft]

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)

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.