cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pradeep2
Frequent Visitor

Incorrect week number - Trasnform Date to Week of Year

Hi All,

I am confused trying to find a solution to this. What we are doing is converting all the dates to weeks of the respective years. Current year works fine but for 2017 - it starts with Week 2 i.e, for 2017 our first entry is 3rd Jan 2017 which is a Tuesday and Power BI considers it as Week 2 which I dont understand how.

I checked for the start day of week in the system and it is Sunday. Even if Power BI somehow assumes the start day to be Monday , the 3rd Jan 2017 should still be the 1st Week of 2017.

Please let me know your thoughts.

Thanks,
Pradeep P

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

instead of duplicating a column and then transforming it, can you add a custom column like this:

Date.WeekOfYear([Column1], Day.Sunday)

the result should be like this:

Capture.PNG



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

9 REPLIES 9
paulj1
Helper II
Helper II

I have this issue transitioning between 2020 & 2021.....

 

Because 1st January 2021 is a friday, it is calling that week 1 ???

 

Then all the week numbers in 2021 are out by 1, note that the black calendar are a snip from outlook.

 

I am using Date.WeekOfYear( [date], Day.Monday ) in Query manager to create the Calendar table that i then use in about 80 x reports !!!  so this is a bit of a problem, as i'm going to have to update all those pbix files....  think i might need a SQL Calendar table on my server !!!

 

Any Ideas ?

 

Capture 4.JPG

 

https://1drv.ms/u/s!ApcyZzyFx2-9hZUv3jRYVjZEyde80Q?e=2Igb1l

jroussee
Frequent Visitor

Hi! How did you solve your problem?

I have the same issue that Power BI sets 1,2 and 3 January of 2021 as week 1, instead of week 53 of 2020. 

Hi,

Try creating a separate formulated column as mentioned by Stachu. Or just as a workaround use a calculated column by subtracting 1 from every week.

Stachu
Community Champion
Community Champion

which function are you using for the week conversion?
https://docs.microsoft.com/en-us/powerquery-m/date-weekofyear

in M you can specify the beggining of the week explicitly, so you could switch it to Sunday

in your case I assume the situation is following:

Jan 1st is in a week commencing Dec 26th, marking it week 1 (effectively with only one day in Jan)

Jan 3rd is in week commencing Jan 2nd making it week 2



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Pradeep2
Frequent Visitor

Hi Stachu,

Many thanks for responding.

I have not used M but have used the Date function in Transform where we get the week of year. Now, according to what you have said , "Jan 1st is in a week commencing Dec 26th, marking it week 1" - I wonder how Jan 1st would be taken in Week 1 as it would be included in the last week of 2016 which makes Week 1 to start from Jan 2 2017.

I dug a bit more and have gotten closer to the problem : We are deploying the reports on Power BI Service from our Windows Server which seems where the issue lies. As you see below Windows 10 shows the correct Week number, but the Server one does not :

Week Mismatch.png

 Now, just need to figure how this happens.

Stachu
Community Champion
Community Champion

if Jan 1st would be in Week 53 then following that logic 2-8th Jan would be week 54, 9-15th would be week 55 etc.
right now it's like this:
Dec 26-31 is week 53, Jan 1st is week1 of 2017, Jan 2-8 is week 2 of 2017

if you look at the formula bar starting with Table.Transform you will noice there Date.WeekOfYear function being used
you just need to add Day.Sunday after your column reference to start the week on Sunday

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Pradeep2
Frequent Visitor

Still the same 😞

Week Mismatch1.PNG

Stachu
Community Champion
Community Champion

instead of duplicating a column and then transforming it, can you add a custom column like this:

Date.WeekOfYear([Column1], Day.Sunday)

the result should be like this:

Capture.PNG



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

Pradeep2
Frequent Visitor

Thanks a lot !  That did it 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.