Forum Discussion

DonalMc's avatar
DonalMc
Advocate II
3 years ago
Solved

Sort column for Week Period Spanning the Year

I have added a column to my Power Query date table called Week Dates, as below, to get a result like "Jan-25-2010 to Jan-31-2010" Week Dates = Date.ToText( Date.StartOfWeek([Date],firstDayofWeek), ...
Need Help
  • DonalMc's avatar
    DonalMc
    3 years ago

    Got this sorted, see below for anyone else trying to achieve same

     

    First Weekday Year = 

    if Date.StartOfWeek([Date],firstDayofWeek) <= Date.StartOfYear([Date]) then Date.StartOfYear([Date]) else Date.StartOfWeek([Date],firstDayofWeek)

     

    Last Weekday Year = 

    if Date.EndOfWeek([Date],firstDayofWeek) >= Date.EndOfYear([Date]) then Date.EndOfYear([Date]) else Date.EndOfWeek([Date],firstDayofWeek)

     

    Week Dates =

    Date.ToText([First Weekday Year], "MMM-dd-yy")&" to "& Date.ToText([Last Weekday Year], "MMM-dd-yy")

DonalMc's avatar
DonalMc
Advocate II
3 years ago

Got this sorted, see below for anyone else trying to achieve same

 

First Weekday Year = 

if Date.StartOfWeek([Date],firstDayofWeek) <= Date.StartOfYear([Date]) then Date.StartOfYear([Date]) else Date.StartOfWeek([Date],firstDayofWeek)

 

Last Weekday Year = 

if Date.EndOfWeek([Date],firstDayofWeek) >= Date.EndOfYear([Date]) then Date.EndOfYear([Date]) else Date.EndOfWeek([Date],firstDayofWeek)

 

Week Dates =

Date.ToText([First Weekday Year], "MMM-dd-yy")&" to "& Date.ToText([Last Weekday Year], "MMM-dd-yy")