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.
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?
Solved! Go to 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
Hi @Anonymous ,
DAX should be a work around.
Column =
VAR weecurrent =
WEEKNUM ( TODAY (), 1 )
RETURN
WEEKNUM ( [date], 1 ) - weecurrent + 1
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.
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
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.