Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"))
@Seb90 , Try to change the column like
"Jahr-KW", Year([Date)*100 + WEEKNUM ( [Date], 21)
for correct sorting you have to use Year Week
Hey @amitchandak, thanks for your fast respons.
I tried it out but in the visual I have still the same Problem.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |