If statement in M code year and weeknumber

[bartschulz]

if [VERVALDATUM origineel] < DateTime.LocalNow()
then Date.WeekOfYear (Date.AddDays (DateTime.LocalNow(),-7))
else Date.WeekOfYear ([VERVALDATUM origineel])

This statement works to get the weeknumbers. But how do i get also the year and weeknumber like 202025 (then) and 202026 (else)

using M code

Eventualy i want to sort on the weeknumbers chonology

[kriscoupe · Accepted solution]

Hi @bartschulz,

A good way to approach this is to take the year multiplied by 100 and then add the week numbers that you always get that 6 digit format even when week numbers are single digit numbers ie year is 2020 *100 = 202000 + 1 = 202001 for week 1. So something like

if [VERVALDATUM origineel] < DateTime.LocalNow()
then Date.Year([VERVALDATUM origineel]) * 100 + Date.WeekOfYear (Date.AddDays (DateTime.LocalNow(),-7))
else Date.Year([VERVALDATUM origineel]) * 100 + Date.WeekOfYear ([VERVALDATUM origineel]

I don't fully understand the middle criteria why it defaults to using a week before today but hopefully you catch my drift!

Kris

[bartschulz]

@kriscoupe

Thanks for the explanation. I saw your solution earlier but did not understand the  *100. Now I know why you use it. 

I was looking the wrong way thought in way like &year&week but your way works.

The reason for the then line is that I want to see which payments I am due, instead of  calculate them with the current week payments.