Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Monthly X-Axis with Weekly Data on Line Chart

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)

 

Need Help

8 Replies

  • Washivale's avatar
    Washivale
    Resolver V
    7 years ago

    Hi Anonymous ,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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.

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, Anonymous 

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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).