Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
carlbh
Helper I
Helper I

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

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.

3 REPLIES 3
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

 

Call Stats.png

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)

test_last_26week_from_monday.PNG

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.