Forum Discussion
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
- amitchandak
Super User
Anonymous , Try to change the column like
"Jahr-KW", Year([Date)*100 + WEEKNUM ( [Date], 21)
for correct sorting you have to use Year Week
- AnonymousNot applicable
Hey amitchandak, thanks for your fast respons.
I tried it out but in the visual I have still the same Problem.