cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Counting weeks with week starting on saturday

@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
Highlighted
Helper I
Helper I

Re: Counting weeks with week starting on saturday

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
)

Highlighted
Microsoft
Microsoft

Re: Counting weeks with week starting on saturday

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

Highlighted
Community Champion
Community Champion

Re: Counting weeks with week starting on saturday

@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

Highlighted
Frequent Visitor

Re: Counting weeks with week starting on saturday

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

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors