Forum Discussion
Double X-axis with duplicates
Hello.
I am trying to represent a variable over weeks and I would like to state the month for each week, for which I use a double X-axis with Month and Week.
However, when I represent the variable over 2 different years, weeks are split into different months, as expected:
Is there a way to sort the axis so that the months are in the correct order regardless of the presence of duplicate weeks?
I found the solution, initially following the approach v-yanjiang-msft stated in the following post:
https://community.fabric.microsoft.com/t5/Desktop/Add-weeknr-to-date-hierarchy/td-p/2293820
I created a custom date hierarchy, including Year, Month, Month Name, Week and XWeek (this is the custom order I need for my weeks, starting to order weeks from week 34 onwards). This just does not solve the issue of sorting when using a combined X-axis.
I sorted that out creating a custom order for months, XMonth, the same way I did for weeks. Then, I just ordered Month by XMonth, providing the desired result. This sorting method can be applied as well to Month Name:
5 Replies
- rajendraongole1
Super User
Hi NsHrTr - Create a calculated column in your calendar table that combines Year, Month, and Week into a single value.
eg: YearMonthWeek =
FORMAT(CalendarTable[Year], "0000") & "-" &
FORMAT(CalendarTable[MonthNumber], "00") & "-" &
FORMAT(CalendarTable[WeekNumber], "00")try this column ensures each week is uniquely identified by year and month.
In visual add YearMonthWeek as the X-axis for your chart.In the Modeling tab, select the YearMonthWeek column and ensure it is sorted by the YearMonthWeek column itself.
If you don't want the X-axis to show the composite column in your visual, create another column for display purposes
try the above steps, it works.
DisplayMonthWeek =
CalendarTable[MonthName] & " " & CalendarTable[WeekNumber]Hello rajendraongole1
and Happy New Year first.
Thank you for your reply.
Trying this will represent the 2 years in a chronological order, not being able to compare 2 measures in the same week period unfortunately.
Uzi2019Community Champion
Hi NsHrTr
Create one column which consist of Month & week together. then sort the value in asc orer.
then sort the week column by Newly created column.
Monthweek = 'Calendar'[Month No]&'Calendar'[week]sort your week column by using Monthweek columnI hope I answered your question!Hello Uzi2019,
and Happy New Year first!
Your solution seems easy to apply but I am unable to replicate as some week numbers relates to different months, and that lack of singularity prevents me to sort week number by the created measure.
I need to say that I am representing months and weeks from the calendar table, while measure is from facts table. This may be causing the issue.
Kind regards.
I found the solution, initially following the approach v-yanjiang-msft stated in the following post:
https://community.fabric.microsoft.com/t5/Desktop/Add-weeknr-to-date-hierarchy/td-p/2293820
I created a custom date hierarchy, including Year, Month, Month Name, Week and XWeek (this is the custom order I need for my weeks, starting to order weeks from week 34 onwards). This just does not solve the issue of sorting when using a combined X-axis.
I sorted that out creating a custom order for months, XMonth, the same way I did for weeks. Then, I just ordered Month by XMonth, providing the desired result. This sorting method can be applied as well to Month Name:
