How to get a MMM-formatted X-axis with week-level data granularity, without aggregating to month?

[MarkNoordraven]

Hi all,

I've got a table with 2 columns, values and weekly dates. Plotting it into a line chart and removing the date hierarchy results in the following chart:

What I'd like to do is change the X-axis format to "MMM" instead of the current "Mmm YYYY", while retaining the weekly tooltips as in current example and while retaining the weekly character of the line.

Things I tried:

• Creating a custom date hierarchy of year-month-week-day.
• Creating a separate date DAX-based calendar table with week numbers and dates formatted in "MMM".
• Creating a separate Power Query based calendar table with week numbers and dates formatted in "MMM".

Challenges I run into:

• These solutions result in a monthly line in which the monthly data points are an average of the underlying weekly data points.
• The X-axis formatted as "MMM" is sorted alphabetically.

[v-yuta-msft]

Hi MarkNoordraven,

Click format-> X axis and change continuous to categorical type.

 

Regards,

Jimmy Tao

[MarkNoordraven]

Hi Jimmy,

Thank you for your response.

The proposed solution doesn't appear to work for me unfortunately, because of these reasons:

- The line turns into a monthly line in which each point on the chart is an average of the underlying weeks in the month.

- The months are sorted in an inappropriate order.

When I try to change the order in the Modeling tab, I get the following error:

[v-yuta-msft]

Hi MarkNoordraven,

Change "Average of Value" to "Sum of Value" in the value field. 

 

In addtion, the sort of date is decided by first alphanumeric when the value is text type, so you should change text type to date type.

Regards,

Jimmy Tao

[MarkNoordraven]

Thank you Jimmy for looking into my question.

• It appears formatting into date results in an error.

• Summing Value seems to result in the months in the axis shifting around, but not yet following chronological order.

If it helps, please find below the .pbix file used in these examples: https://we.tl/t-VtukT8XB1x