Forum Discussion
Datesbetween
- 3 years ago
To produce a line chart that shows multiple years worth of data I would suggest the following steps:
- Create a date dimension table (if you don't already have one), with the columns Date, Month and Year.
- Create a relationship between the date dimension table and the merged_data[Date] column.
- Create a measure for Working Hours = SUM( merged_data[WorkingHours])
- Add a line chart visual to your report
- Add the Working Hours measure to the Values section of the line chart
- Add the Month column (from the date dimension table) to the x-axis of the line chart
- Add the Year column from the date dimension table to the Legend section of the line chart.
- 3 years ago
Hi Nas211
In your date dimension table, create the following two columns:
Month Number = MONTH( 'Calendar'[Date] ) Month Name Short = FORMAT( 'Calendar'[Date], "MMM" )In your line chart visual, add Month Name Short to the x-axis. (If you want the full month name, e.g. January rather than Jan, add an extra "m" into the FORMAT function.)
By default the months will be sorted alphabetically. To sort them in calendar order, go to Data view, select the Month Name Short from the fields list, and at the top of the page you'll see "Sort by Column". Click on that and then select "Month Number". This tells Power BI to sort the month name column numerically in order of the month numbers.
Hi, your initial suggestion works great! But I'd like to have these lines stacked. The X axis should only have Single month names like Jan2019 to Jul 2022. Here, the months for each year shows which prevents the trendlines from stacking.
What are your thoughts?
Thanks.
Hi Nas211
In your date dimension table, create the following two columns:
Month Number = MONTH( 'Calendar'[Date] )
Month Name Short = FORMAT( 'Calendar'[Date], "MMM" )
In your line chart visual, add Month Name Short to the x-axis. (If you want the full month name, e.g. January rather than Jan, add an extra "m" into the FORMAT function.)
By default the months will be sorted alphabetically. To sort them in calendar order, go to Data view, select the Month Name Short from the fields list, and at the top of the page you'll see "Sort by Column". Click on that and then select "Month Number". This tells Power BI to sort the month name column numerically in order of the month numbers.
- Nas2113 years agoRegular Visitor
Thanks. It did work. I appreciate the the inputs.