Double X-axis with duplicates

[NsHrTr]

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?

[NsHrTr · Accepted solution]

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:

[Uzi2019]

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 column

I hope I answered your question!

[NsHrTr]

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.

[rajendraongole1]

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]

[NsHrTr]

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.