From date to week number - formula doesn't work

[rashel2002]

Hi All,

I'm using the following formula to convert date to work-week:

Number.ToText(Date.WeekOfYear([MSP_EpmAssignmentByDay_UserView.TimeByDay]))

But then i get 53 as a work week, even though there is no ww53 in 2018.

How can i solve it? 

Thanks!

[v-shex-msft · Accepted solution]

Hi @rashel2002,

AFAIK, date functions not works for datetime type, I'd like to suggest you add Date.From function into your formula to extract date value.

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.From(Date.WeekOfYear(Date.From([MSP_EpmAssignmentByDay_UserView.TimeByDay]))))

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3MNQ3MjA0VzA0sjIytDI0UIrVAQoDBcHiFgogMStDI6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.From(Date.WeekOfYear(Date.From([Date]))))
in
    #"Added Custom"

Regards,

Xiaoxin Sheng

[Omega]

If you still want it in M Query, check the below link: 

http://datacornering.com/how-to-calculate-iso-week-number-in-power-query/

[Omega]

Try creating a calculated column and use the below formula: 

Week = WEEKNUM('Calendar'[Date],21)

[AlB]

Hi @rashel2002

Can you share your file? Other factors might be playing a role

[rashel2002]

Hi, thanks for responding.

Unfortunately, i can't share the file because it's hold classified data.

But as you can see the formula is related only to the date column.

Here are some problematic dates-

[AlB]

@rashel2002

In these cases, you could share only the part that is not working while taking all the confidential data out. Or recreate the issue with dummy data.