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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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


@ 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!:
Mastering Power BI 2nd Edition

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.