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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GiangLe
Helper I
Helper I

Counting weeks with week starting on saturday

Hi everyone,

 

I'm facing these challenges that
1. i want to count week in a year (from 1 to 52). Weeknum function only gives me 2 options for either monday or sunday as starting day. But i need saturday as my starting day. I hope there is a way to work around this.
2. I also need to distingguish week number between years. for example both 2016 and 2017 have week number 10. when i create a year slicer, it works fine if i choose only one of the year (2016 and 2017) but if i choose both, power bi combines both week 10 of 2016 and 2017 while what i wanna see is the difference/development between the 2

Thanks

1 ACCEPTED SOLUTION

@GiangLe Your formula will give wrong results (week number 1 too high) for Saturdays in years where January 1 is on Saturday (e.g. 2022).

 

My suggestion would be:

 

Weekno = 
If( weekday ([date], 1) < 7 || weekday(date(year([date]),1,1)) = 7,
weeknum([date], 1 ),
weeknum([date], 1) +1
)
Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
GiangLe
Helper I
Helper I

Hi everyone, i found a way to work around problem 1 with IF function, it goes like this in case someone will need it in the future
Weekno =
If( weekday (date, 1) < 7,
weeknum(date ,1 ),
weeknum(date, 1) +1
)

Hi @GiangLe,

I am gald to hear you have resolved your issue by yourself. Please mark the right/helpful reply as answer. More people will learn new things here. 

Thanks,
Angelia

@GiangLe Your formula will give wrong results (week number 1 too high) for Saturdays in years where January 1 is on Saturday (e.g. 2022).

 

My suggestion would be:

 

Weekno = 
If( weekday ([date], 1) < 7 || weekday(date(year([date]),1,1)) = 7,
weeknum([date], 1 ),
weeknum([date], 1) +1
)
Specializing in Power Query Formula Language (M)

This worked worked wonders for me. I was trying to do it for Friday so changed it to 6 and it worked.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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