Forum Discussion
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), "MMM-dd-yyyy")&" to "& Date.ToText(Date.EndOfWeek([Date],firstDayofWeek), "MMM-dd-yyyy")
I am unable to create a sort column for this column because the column spans the year, and I get the we can't sort by this column because there can't be more than one value message - you can see the problem below.
I think the best approach is to limit the Week Dates column to a periond that does not span the year - any suggestions as to how I would do this, or any other suggestions as to the sort column?
Thanks
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")
1 Reply
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")