Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
KierONeil
Frequent Visitor

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**

KierONeil_0-1708130206540.png

 

 

What am I missing?

 

I appreciate any help on this. I couldn't think of a good way to Google it

 

YearQuarterMonthDaySales Amount
2018Qtr 1March1$186.75
2018Qtr 1March2$153.13
2018Qtr 1March3$72.86
2018Qtr 1March4$154.97
2018Qtr 1March5$83.64
2018Qtr 1March6$144.19
2018Qtr 1March7$99.87
2018Qtr 1March8$171.03
2018Qtr 1March9$180.55
2018Qtr 1March10$73.33
2018Qtr 1March11$111.19
2018Qtr 1March12$110.37
2018Qtr 1March13$130.29
2018Qtr 1March14$76.29
2018Qtr 1March15$149.85
2018Qtr 1March16$140.71
2018Qtr 1March17$161.84
2018Qtr 1March18$98.14
2018Qtr 1March19$58.54
2018Qtr 1March20$152.28
2018Qtr 1March21$112.55
2018Qtr 1March22$135.49
2018Qtr 1March23$152.94
2018Qtr 1March24$51.80
2018Qtr 1March25$114.37
2018Qtr 1March26$67.47
2018Qtr 1March27$119.64
2018Qtr 1March28$61.97
2018Qtr 1March29$73.50
2018Qtr 1March30$163.32
2018Qtr 1March31$61.67
2018Qtr 2April1$95.03
2018Qtr 2April2$97.50
2018Qtr 2April3$151.31
2018Qtr 2April4$74.47
2018Qtr 2April5$89.43
1 ACCEPTED SOLUTION

I recommend you invest in a proper calendar table.

View solution in original post

11 REPLIES 11
v-yiruan-msft
Community Support
Community Support

Hi @KierONeil ,

Please navigate to Format pane and change Type as "Category" in X-axis option. 

vyiruanmsft_0-1708419589813.png

vyiruanmsft_1-1708419700450.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

KierONeil_1-1708565833843.png

 

Arul
Super User
Super User

@KierONeil ,

Do you have active relationship between Purchasing Date and sales table?

Did you mark your Purchasing Date table as date table?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


Online Sales is a standalone table. The only connection is to a Geo table.  It does not have a date table only the Purchasing Date field which is formatted as a date.

I recommend you invest in a proper calendar table.

I don't have the underlying data in this visual to test it but I think you may be right about the need for a sales table. I will test this out the next opportunity I get. 
We can consider this issue closed. I appreciate the time and help. 

lbendlin
Super User
Super User

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

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/1447...

I updated the issue and added images and data. 

 The date is formated as a date and it has the hierarchy. 

I recommend you invest in a proper calendar table.  Don't use auto date/time or any other hierarchies.  The visual will automatically do that for you.

Arul
Super User
Super User

@KierONeil ,

Can you share your current output screenshot and some sample data? If possible, could please explain the expected output in detail?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


I updated the issue and added images and data. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors