Transform date to weeknumer in year gives the wrong result

[Anders_No]

I have a date - and by right-clicking on the header -> Transform -> Week -> Week of year, I get the weeknumber.

However, the weeknumber provided does not fit with the calendar - as we should be in week 1now.

Power BI starts with week one 1. January which is a Friday and not a Sunday, which I read is the start day in Power BI (though I think it should be Monday).

Anyone having the same isue/know whar to to?

[v-lili6-msft]

hi  @Anders_No 

Just add a parameter in the M code as below manually:

= Table.AddColumn(#"Changed Type", "Week of Year", each Date.WeekOfYear([Date],Day.Monday), Int64.Type)

https://docs.microsoft.com/en-us/powerquery-m/date-weekofyear

Syntax

Date.WeekOfYear(dateTime as any, optional firstDayOfWeek as nullable number) as nullable number

About

Returns a number from 1 to 54 indicating which week of the year the date, dateTime, falls in.

• dateTime: A datetime value for which the week-of-the-year is determined.

• firstDayOfWeek: An optional Day.Type value that indicates which day is considered the start of a new week (for example, Day.Sunday. If unspecified, a culture-dependent default is used.

Regards,

Lin

[Anonymous]

I have a feeling the problem is much broader. DAX WEEKNUM also gives incorrect values since 1 jan 2021 compared to the Outlook calendar and a google search.

[Anders_No]

Hi @v-lili6-msft 

thankyou very much!

However I use direct query, which does not support the solution...

I changed to import mode (which for now could be usable) but now I get an error:

Expression.Error: We cannot apply field access to the type Date.
Details:
Value=16-12-2013
Key=Date

I guess it's because it can't recognize the Danish date-format?

Thus, I tried to change regional settings to Danish and started a new report, as it will not work in existing reports.

However, I still get the same error-message...

Anders.

[v-lili6-msft]

hi @Anders_No 

clicking on the header -> Transform -> Week -> Week of year, I get the weeknumber.

please try the way as below
clicking on the header -> Add Column -> Week -> Week of year, then add a parameter in the M code and get the weeknumber.

Now you could remove basic date column.

here is my M code, please try it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdC5CcAwFATRXhQbpN0vX7UI99+GcWDYCSd7zFpNXd3Das/2hTMqY2bsGUfGmXFl3BkaKBgEhKAQGIJDgAgSgSJYDIv5AxbDYlgMi2ExLIbFsBQsFRZ3hDMqY2b8qOcF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Inserted Week of Year" = Table.AddColumn(#"Changed Type", "Week of Year", each Date.WeekOfYear([Date],Day.Monday), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Week of Year",{"Date"})
in
    #"Removed Columns"

Regards,

Lin

[Anders_No]

Thankyou very much again @v-lili6-msft!

I tried adding a column as suggested, however the weeknumers are the same..,

My code is:

= Table.AddColumn(#"Renamed Columns3", "Week of Year", each Date.WeekOfYear([Created],Day.Monday), Int64.Type)

I'm not sure what part of your code I alternately should copy/paste where - I don't have a lot of coding experience...

Regards

Anders.

[Anonymous]

Hi @Anders_No 

What is your expectation?

Isnt this the same problem as here? https://community.powerbi.com/t5/Issues/DAX-WEEKNUM-gives-incorrect-values-since-jan-1st-2021/idi-p/...

That post is about DAX WEEKNUM but maybe it behaves exactly the same as your problem (maybe shared code)?

[Anders_No]

Hi @Anonymous 

yes, that's exactly the same problem!

[v-lili6-msft]

hi  @Anders_No 

here is my simple sample pbix file, please try it.

and please share your sample data and your expected output.

Regards,

Lin

[Anders_No]

Hi @v-lili6-msft 

I have uploaded my sample file here.

Regards

Anders.

[v-lili6-msft]

hi  @Anders_No 

I check your sample pbix file, do you mean that weeknum for 1-1-2021 / 2-1-2021 / 3-1-2021 should be 53? it likes ISO 8601 definition?

if so, it couldn't be achieve that use built-in function to get it in power bi for now, because this is designed by default like DAX WEEKNUM function in power bi for now, I would suggest you use the logic to custom coding it by dax or do it in datasource(SQL) then import it.

This is documented here: https://docs.microsoft.com/en-us/dax/weeknum-function-dax

Regards,

Lin