Forum Discussion
Time-Series Line chart multi-year grouping (Updated)
I'll preface this with I'm still learning Power BI so this may be one of those issues that you've seen a million times. I hope so because it's bugging me.
The situation is that I have multi-year data, let's say 2018, 2019, and 2020 and it's at a daily granularity. I put the date in the x-axis of the line chart. The date is formated as a date and it has the hierarchy. I put the Total Sales on the y-axis.
When I drill down to month level it shows the sum of all sales in January, February, etc adding together all the years for that month instead of showing monthly datapoints from starting in 2018 and ending in 2020.
Same thing happens when I drill down to Day. My x-axis goes from 1 to 31 instead of giving me a daily totals spanning the three years. **Data at Bottom**
What am I missing?
I appreciate any help on this. I couldn't think of a good way to Google it
| Year | Quarter | Month | Day | Sales Amount |
| 2018 | Qtr 1 | March | 1 | $186.75 |
| 2018 | Qtr 1 | March | 2 | $153.13 |
| 2018 | Qtr 1 | March | 3 | $72.86 |
| 2018 | Qtr 1 | March | 4 | $154.97 |
| 2018 | Qtr 1 | March | 5 | $83.64 |
| 2018 | Qtr 1 | March | 6 | $144.19 |
| 2018 | Qtr 1 | March | 7 | $99.87 |
| 2018 | Qtr 1 | March | 8 | $171.03 |
| 2018 | Qtr 1 | March | 9 | $180.55 |
| 2018 | Qtr 1 | March | 10 | $73.33 |
| 2018 | Qtr 1 | March | 11 | $111.19 |
| 2018 | Qtr 1 | March | 12 | $110.37 |
| 2018 | Qtr 1 | March | 13 | $130.29 |
| 2018 | Qtr 1 | March | 14 | $76.29 |
| 2018 | Qtr 1 | March | 15 | $149.85 |
| 2018 | Qtr 1 | March | 16 | $140.71 |
| 2018 | Qtr 1 | March | 17 | $161.84 |
| 2018 | Qtr 1 | March | 18 | $98.14 |
| 2018 | Qtr 1 | March | 19 | $58.54 |
| 2018 | Qtr 1 | March | 20 | $152.28 |
| 2018 | Qtr 1 | March | 21 | $112.55 |
| 2018 | Qtr 1 | March | 22 | $135.49 |
| 2018 | Qtr 1 | March | 23 | $152.94 |
| 2018 | Qtr 1 | March | 24 | $51.80 |
| 2018 | Qtr 1 | March | 25 | $114.37 |
| 2018 | Qtr 1 | March | 26 | $67.47 |
| 2018 | Qtr 1 | March | 27 | $119.64 |
| 2018 | Qtr 1 | March | 28 | $61.97 |
| 2018 | Qtr 1 | March | 29 | $73.50 |
| 2018 | Qtr 1 | March | 30 | $163.32 |
| 2018 | Qtr 1 | March | 31 | $61.67 |
| 2018 | Qtr 2 | April | 1 | $95.03 |
| 2018 | Qtr 2 | April | 2 | $97.50 |
| 2018 | Qtr 2 | April | 3 | $151.31 |
| 2018 | Qtr 2 | April | 4 | $74.47 |
| 2018 | Qtr 2 | April | 5 | $89.43 |
I recommend you invest in a proper calendar table.
11 Replies
I updated the issue and added images and data.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi KierONeil ,
Please navigate to Format pane and change Type as "Category" in X-axis option.
Best Regards
You were right that I did have the x-axis set as Continuous and I changed it to Category and it looks like the image you show.
Unfortunately, It doesn't resolve the issue that I'm having.
What I expect to see is a line chart of daily sales from the start to the end like this example below. What I'm getting is the sum of sales on individual day numbers across the duration which isn't useful to me.
I looked at the x-axis on this chart and it's set to Continuous, if it matters. Other than that, it's the same as the chart I'm having issues with.
