I don't want week 53

[Irache]

Hello,

I have this M code : "Text.From(Date.Year([#"Date livr."])) & Text.PadStart(Text.From(Date.WeekOfYear([#"Date livr."], Day.Sunday)), 2, "0")"

"This formula returns a week format like '202345'. The issue is that I don't want this function to incorrectly represent week 53. I observe that it returns week 53 when the date is 31/12/2023. However, according to the calendar, it is displayed as week 52. I'm trying to understand why there is a discrepancy between the obtained result and the calendar."

[collinsg]

Good day Irache,

Could the issue be as follows? The year 2023 has 365 days which is 52 weeks and 1 day. There are therefore 53 distinct 7-day periods and a classification by week number must have 53 distinct values. Perhaps your calendar is indexing these from 0 to 52. If this is the case then a solution is to subtract 1 from the week number in your formula.

Date.WeekOfYear([#"Date livr."]) - 1

Hope this helps

[v-xinruzhu-msft]

Hi @Irache 

You can add a custom column and input the following code

let a=Date.DayOfWeek([#"Date livr."],Day.Monday),
b= if Date.Month([#"Date livr."])=12 then Date.AddDays([#"Date livr."],-a) else [#"Date livr."]
in Text.From(Date.Year(b)) & Text.PadStart(Text.From(Date.WeekOfYear(b)), 2, "0")

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Irache]

Hello @v-xinruzhu-msft 

I have applied your formula, but it seems to be ineffective. The week 52, which start from 25/12/2023 to 31/12/2023, is erroneously identified as week 53.

[v-xinruzhu-msft]

Hi @Irache 

The code can work in my sample

Can you provide the code you input?

Best Regards!

Yolo Zhu

[Irache]

I have copied the same code.

let a=Date.DayOfWeek([#"Date livr."],Day.Monday),
b= if Date.Month([#"Date livr."])=12 then Date.AddDays([#"Date livr."],-a) else [#"Date livr."]
in Text.From(Date.Year(b)) & Text.PadStart(Text.From(Date.WeekOfYear(b)), 2, "0")

[v-xinruzhu-msft]

Hi @Irache 

Or you can consider to use the weeknum() function in power bi desktop, create a calculated column and input the following code

Column 2 = YEAR([Date livr.])&WEEKNUM([Date livr.],21)

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Irache]

I have already  thought about that but I need to make this with Power Query. However, I have found a solution with this code :

let a = Text.From(Date.Year([#"Date livr."])),
b = if Text.PadStart(Text.From(Date.WeekOfYear([#"Date livr."], Day.Sunday)), 2, "0") = "53" 
then "52" else Text.PadStart(Text.From(Date.WeekOfYear([#"Date livr."], Day.Sunday)), 2, "0") in a&b