Sorting by Start of week date

[Nicola]

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)

[Melissa]

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)

[sam.mckay]

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

Dynamic Date Query Table

Hi @avalon.ds , I use M code to get the first and last date on my sales or fact table and then add an amount of days to it: Normal date table: [image] M date table: [image] The difference is in the Source, you create a normal date table then go to the source and use M language. List.Min(FactTable[DateColumn]) => this will give you the start date of your FactTable data List.Sum({List.Max(FactTable[DateColumn]), #duration(30, 0, 0, 0)}) => this will get the last date on your FactTable an…

Sam

[Nicola]

Thanks Melissa

[Nicola]

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.