cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver IV
Resolver IV

Re: If statement in M code year and weeknumber

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

View solution in original post

2 REPLIES 2
Highlighted
Resolver IV
Resolver IV

Re: If statement in M code year and weeknumber

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

View solution in original post

Highlighted
Frequent Visitor

Re: If statement in M code year and weeknumber

@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.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors