Forum Discussion
If statement in M code year and weeknumber
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
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
2 Replies
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
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.
