Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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 Sa...
Need Help
  • Geradav's avatar
    Geradav
    6 years ago

    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

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

DAX should be a work around.

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

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.