Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Sorting calendar weeks when this is divided into two years

Hello all,
I have the following problem:

I have built a date table with DAX (see script) and now I want to drill from year to month and then to calendar week in my visualization. But now I have the problem that when I am on the level of the calendar week I do not get them on the X-axis in the correct order. Means, my year 2022 should end with week 52 and the year 2023 should start with week 52, because the 01.01. contains numbers that do not belong to the end of the month January.
I have already tried to change the sorting of the calendar weeks column by calculated columns, but this has always only encountered the following error.

I have also searched the Internet and also various forums, but also there no concrete hint found, and thought me now that maybe someone can help me here.

Code in DAX

DimKalender =

ADDCOLUMNS (

    CALENDAR (  DATE(2020,01,01),  DATE(2025,12,31) ),

    "Jahr", YEAR ( [Date] ),

    "Monat", MONTH ( [Date] ),

    "Monatname", FORMAT ( [Date], "MMMM" ),

    "MonatnameKurz", FORMAT ( [Date], "MMM" ),

    "YYYYMM", FORMAT ( [Date], "YYYYMM" ),

    "YYYY-MMM", FORMAT ( [Date], "YYYY-MMM" ),

    "Quartal", FORMAT ( [Date], "Q" ),

    "QuartalName", "Q" & FORMAT ( [Date], "Q" ),

    "YYYYQ", FORMAT ( [Date], "YYYYQ" ),

    "YYYY-Q", FORMAT ( [Date], "YYYY" ) & "-" & "Q"

        & FORMAT ( [Date], "Q" ),

    "Kalenderwoche","KW" & WEEKNUM ( [Date], 21),

    "Jahr-KW", FORMAT([Date],"YYYY-WW" ),

    "Wochentag", WEEKDAY ( [Date], 2),

    "Wochentagname", FORMAT ( [Date], "DDDD" ),

    "Wochentagname Kurz", FORMAT ( [Date], "DDD" ),

    "YYYY-MM-DD",Format([date],"YYYY-MM-DD"))

2 Replies

  • Anonymous , Try to change the column like

    "Jahr-KW", Year([Date)*100 + WEEKNUM ( [Date], 21)

     

    for correct sorting you have to use Year Week

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hey amitchandak, thanks for your fast respons.

      I tried it out but in the visual I have still the same Problem.