Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.