Need M Code to create new column with current week as "1"

[Anonymous]

I'm using a query to create a Date table. I need a column that shows the current week as "1"; next week as "2"; following week as "3"; etc.  The task is complicated because our work week starts on Saturday and ends on Friday. I have a statement that works, but it seems unnecessarially long:

"each (Number.From(Date.StartOfWeek(Date.AddDays([Date],2)))-Number.From(Date.StartOfWeek(DateTime.LocalNow())))/7+1)"

This code makes last week, "0", and decrements each week in the past from there..., which is fine. But, would like to know, is there a more direct - or more effecient - method to accomplish this task using M Code?

[v-frfei-msft]

Hi @Anonymous ,

DAX should be a work around.

Column = 
VAR weecurrent =
    WEEKNUM ( TODAY (), 1 )
RETURN
    WEEKNUM ( [date], 1 ) - weecurrent + 1

[Anonymous]

Really want to do this in the Query using M code. As for the DAX alternative, the suggested looks like it also works, but it doesn't account for the work week starting on Saturday (in other words, current week ends on Friday). May be an easy fix, but just for anyone who may happen upon this in the future, thought I should include that note. In M code, that turned out to be a challenge (though it works with the entry included in the first post.

Anyway, thanks for the comments. I learn a lot here.

[Geradav · Accepted solution]

HI @Anonymous ,

The short answer is no, you won't find anything more direct than that. What you have is the best one can have.

Regards

David