Incorrect week number - Trasnform Date to Week of Year

[Pradeep2]

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

[paulj1]

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 ?

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

[Anonymous]

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. 

[Pradeep2]

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]

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

[Pradeep2]

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 :

 Now, just need to figure how this happens.

[Stachu]

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

[Pradeep2]

Still the same 😞

[Stachu · Accepted solution]

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:

[Pradeep2]

Thanks a lot !  That did it 🙂