Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
amalrio
Helper V
Helper V

Start day of the week based on year week number integer

Hi all, 

 

I wanted to calculate a column in a tabel for week start date based on a year week number integer

 

as below..

 

YearWeek              Start Of the Week (new calculated column based on YearWeek column)

20211                    1/1/2021

20212                    4/1/2021

20213                    11/1/2021

....

....

202141                 6/9/2021

202142                 13/9/2021

 

is there any way I can do this. thanks for the help

@Anonymous 

 

  

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

Simple enough with PQ,

let
    Source = List.TransformMany({2021,2022}, each {1..Date.WeekOfYear(#date(_,12,31), Day.Monday)}, (x,y) => {Text.From(x)&"-"&Number.ToText(y,"00"), List.Max({#date(x,1,1), Date.StartOfWeek(#date(x,1,1)+Duration.From((y-1)*7),Day.Monday)})}),
    #"Start of Week" = Table.FromRows(Source, {"yyyyww", "Start of Week"})
in
    #"Start of Week"

Screenshot 2021-10-27 061550.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

2 REPLIES 2
CNENFRNL
Community Champion
Community Champion

Simple enough with PQ,

let
    Source = List.TransformMany({2021,2022}, each {1..Date.WeekOfYear(#date(_,12,31), Day.Monday)}, (x,y) => {Text.From(x)&"-"&Number.ToText(y,"00"), List.Max({#date(x,1,1), Date.StartOfWeek(#date(x,1,1)+Duration.From((y-1)*7),Day.Monday)})}),
    #"Start of Week" = Table.FromRows(Source, {"yyyyww", "Start of Week"})
in
    #"Start of Week"

Screenshot 2021-10-27 061550.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

VahidDM
Super User
Super User

HI @amalrio 

 

It would be better to add a calendar table to your model and use that.

 

BTW, Try this code:

Column = 
Var _WN = FORMAT(right([YearWeek],LEN([YearWeek])-4),"####")
Var _MinYear = CALCULATE(left(min([YearWeek]),4),all('Table'))
Var _MaxYear = CALCULATE(left(Max([YearWeek]),4),all('Table'))
Var _Year = FORMAT(left([YearWeek],4),"####")
Var _DateT = filter(ADDCOLUMNS(calendar(Date(_MinYear,1,1),date(_MaxYear,12,31)),"Year",YEAR([Date]),"WeekN",WEEKNUM([Date]),"Day",format([Date],"ddd")),[Day]<>"Sat"&&[Day]<>"Sun")
Var _FirstDateWeek =ADDCOLUMNS(GROUPBY(_DateT,[WeekN],[Year],"Date",minx(CURRENTGROUP(),[Date])),"YW",CONCATENATE([Year],[WeekN]))
return
MAXX(filter(_FirstDateWeek,VALUE([YW])=[YearWeek]),[Date])

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.