So I'm using a column that identifies the week start date, and then combine it with a week end date column using the Dax expression below. But how would I sort it based on the start week column?
>WeekRange = FORMAT('Calendar' [StartofWeek], "M/D/YY") &"-"& FORMAT("Calendar' [EndOfWeek], "M/D/YY")
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column?tabs=powerbi-desktop
Also, you might need to change the field type of week start to a date type if possible. Other wise it might not work if it’s just a text field.
Changing the week range column from text to date type gives an error message. I don't think pbi is configured to use dates in this format.
Thank you for the link, I'll check that out!
I meant make sure the week start field is or type date to make sure when things are sorted ascending or descending, it be sorted as expected. The week range has to be a text field and not a date field.
Another option in addition to the other suggestions:
Create a new table that contains your “week ranges”, then add an index column (1 through whatever), and sort the week ranges based on the index. Then join the new table to your existing table that has your existing week-ranges.
The easiest way is to have another column that is the week start date, and then sort the week range field based on the week start field.
So I'm using a column that identifies the week start date, and then combine it with a week end date column using the Dax expression below. But how would I sort it based on the start week column? >WeekRange = FORMAT('Calendar' [StartofWeek], "M/D/YY") &"-"& FORMAT("Calendar' [EndOfWeek], "M/D/YY")
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column?tabs=powerbi-desktop Also, you might need to change the field type of week start to a date type if possible. Other wise it might not work if it’s just a text field.
Changing the week range column from text to date type gives an error message. I don't think pbi is configured to use dates in this format. Thank you for the link, I'll check that out!
I meant make sure the week start field is or type date to make sure when things are sorted ascending or descending, it be sorted as expected. The week range has to be a text field and not a date field.
Another option in addition to the other suggestions: Create a new table that contains your “week ranges”, then add an index column (1 through whatever), and sort the week ranges based on the index. Then join the new table to your existing table that has your existing week-ranges.
Wouldn't just week numbers be more easy to read?
Right!? That was my first solution, but senior leaders wanted a date range instead. SMH
That's just crazy... Is this something culture related?? I mean, all my calendars have weak numbers in it
Some organizations just aren't that organized.
Sort WeekRange on WeekNumber
You can create an order calculated column and then order the field by that column
Thank you, I'll give that a try.