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
Pradeep2
Helper I
Helper I

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 🙂

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

Anonymous
Not applicable

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 🙂

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 🙂

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 🙂

Thanks a lot !  That did it 🙂

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.