Monthly X-Axis with Weekly Data on Line Chart

[AROBERT93]

Hi folks!

I have weekly data over multiple years that I'm trying to display in a line chart. The goal is to show one line for each year, with 52 data points intact, but on an X-Axis that only shows the starting point for each month (January-December). Every attempt I've made to put Months in the X-Axis of the chart ends up summarizing/averaging the weekly data points. I can drill up/down, but that's not what I want - would like to see weekly data on a monthly x-axis. Any idea how to make this work? Excel can do it just fine, as shown in the example below:

Weekly Data on Monthly X-Axis (Excel)

[v-lili6-msft]

hi, @AROBERT93 

Just drag Month column into Axis and Year column into Legend.

If not your case, please share a simple sample pbix file for us have a test.

Best Regards,

Lin

[AROBERT93]

Hi Lin!

The data is weekly - putting Month in the X-Axis summarizes the data, which I don't want. I'm looking to preserve the weekly data in the lines, while only showing months on the chart.

This is the summarized view that I want to avoid:The underlying data is weekly - this view summarizes the output (not ideal).

[v-lili6-msft]

hi, @AROBERT93 

Drag WeekNum field into Axis as below, then Expand all and expand one field at a time.

https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill

Best Regards,

Lin

[AROBERT93]

Hi Lin!

We are getting much closer! The output now shows month and Week Number, but the Months repeat. Is there any way to group them so it only shows the Month Name once per Month?Months still repeat...

Appreciate all the help!

[v-lili6-msft · Accepted solution]

hi, @AROBERT93 

Just turn off Concatenate labels in Formatting->X axis->Concatenate labels

Result:

By the way: Please ensure the chart sort by x axis values.

https://community.powerbi.com/t5/Desktop/concatenate-Labels-does-not-work-properly/td-p/415010

Best Regards,

Lin

[AROBERT93]

Hi Lin!

That works! Many thanks for all your help - both X-Axes needed to be numbers (I had been formatting the Month names as text with 'MMM' as the format). I was able to achieve the result by switching back to "Date Hierarchy" for the CalDates and Keeping the Week Numbers as categorical numbers. It looks great!

[Washivale]

Hi @AROBERT93 ,

Assuming you are adding Week Ending/Week Starting date in Line Chart, consider changing X axis Type to "Categorical" using Format option of Visual, let me know if it works. 

Also, you should look for options like showing last 16-24 weeks in single visual instead of 52 weeks). Adequate data points will make your visual better to understand for end users.

Thank you,

Sandeep

[AROBERT93]

Thanks for the reply, Sandeep!

These are the settings I'm using:CalDate is normalized to 2019 dates for prior years

CalDate is set as Categorical

Here is what I get when I set the X-Axis to Categorical (and standardize the weekly dates to all be in the same year - 2019):Weekly Dates on Axis, Set as Categorical

What I'm hoping to achieve is the same view, but with only Months on the X-Axis, as in the original post's Excel example.