M code for dynamic week in Power Query

[Anonymous]

Dear all,

I'm facing issue with M code for dynamic week in Power Query, appreciate your advice.

I create a M code to define week# based on the current date as below. There is not issue to define the current week. But, the issue happends in the transition of year for the last weeks if I use the formula as below. This formula looks like cannot define the start or end week of a year.

If I but the current week - 25, the result is 2021-4 instead of week in 2020.

Appreciate your advice!

My code: Number.ToText(Date.Year(DateTime.Date(DateTime.LocalNow()))) & Number.ToText(Date.WeekOfYear(DateTime.Date(DateTime.LocalNow()))-25)

[mahoneypat]

Please try this expression instead.

= let date1 = Date.AddWeeks(Date.From(DateTime.LocalNow()), -25) in Number.ToText(Date.Year(date1)) & Number.ToText(Date.WeekOfYear(date1))

or you can skip the let and in, and just use the Date.AddWeeks part twice in your original expression

Pat

[Anonymous]

Hi @mahoneypat ,

Thanks for your advice!

I still have an issue with the number of week.

Could you please advsie how can I adjust to return the value is "202101" instead of "20211" for week 1 to week 9?

[mahoneypat · Accepted solution]

Here is modified version that should do that

= let date1 = Date.AddWeeks(Date.From(DateTime.LocalNow()), -25) in Number.ToText(Date.Year(date1)) & Text.PadStart(Number.ToText(Date.WeekOfYear(date1)),2,"0")

Pat

[Anonymous]

Thanks for your support!