Sorting by Start of week date

Hi,

I created a date table using the Calendar dax function (instead of the date code provided by Sam for power query) as I needed one where the mix and max date depended on the date field coming from one of the tables.
I created custom calculated columns for a Start of week column (type date) and a week start column (type text). I need a way to sort these two columns. I created another calculated column to sort it but powerbi won’t let me sort based off this calculated column as it references the column I want to sort. see screenshots.



Is there another way to sort these column?

Also attached the pbix file. forum test.pbix (222.4 KB)

Hi Nicola,

You were so close to solving it! The problem you have is acually one of cardinality…
I made a few changes to your calendar table because you don’t need the calculated columns:

Dates = 
ADDCOLUMNS(
    ADDCOLUMNS (
        CALENDAR (DATE(YEAR(MIN(costs[date]))|1|1)| DATE(YEAR(MAX(costs[date]))|12|31))|
        "DateAsInteger"| FORMAT ( [Date]| "YYYYMMDD" )|
        "Year"| YEAR ( [Date] )|
        "Monthnumber"| FORMAT ( [Date]| "MM" )|
        "YearMonthnumber"| FORMAT ( [Date]| "YYYY/MM" )|
        "Month Name Short"| FORMAT ( [Date]| "mmm" )|
        "Month Name"| FORMAT ( [Date]| "mmmm" )|
        "Month & Year"| FORMAT([Date]| "mmm yyyy")|
        "Day Of Week Number"| WEEKDAY ( [Date]|2 )|
        "Day Of Week"| FORMAT ( [Date]| "dddd")|
        "Day Of Week Short"| FORMAT ( [Date]| "ddd" )|
        "Quarter"| "Q" & FORMAT ( [Date]| "Q" )|
        "Quarter & Year"| "Q" & FORMAT([Date]|"Q/YYYY")|
        "Week Number & Year"| "Wk " & WEEKNUM([Date]|2) & "-" & Year([Date])|
        "Week Number"| WEEKNUM([Date]|2)|
        "WeekYearNumber" | Year([Date]) & 100 + WEEKNUM([Date]|2)|
        "MonthYearNumber"| YEAR([Date]) & 100 + MONTH([Date])|
        "WeekStart"| ( [Date] +1 ) - WEEKDAY ( [Date] | 2 ))|
    "StartOfWeekSort2" | Year([WeekStart]) & 100 + WEEKNUM([WeekStart]|2)|
    "WeekStart2" | "wk " & FORMAT([WeekStart]| "("& "dd"& "MMM" & "YY" & ")")
)

As you can see I added the short year to [WeekStart2] this solves you cardinality issue and then you are able to sort by [StartOfWeekSort2] without any problems. Finally I marked your calendar table as a Date Table (you can find that on the Modeling ribbon).

Here’s you PBIX file: forum test.pbix (239.8 KB)

3 Likes

Check out the bottom of this thread for an idea of how to create dynamic dates even with the Power Query code for the date table

Sam

1 Like

Thanks Melissa :slight_smile:

Thanks Sam. I knew there was a way to do it but Im still learning the M language so went with DAX instead. But thanks for this.