Forum Discussion
YOY Line Chart
Hi Anonymous
As tested, You need to add a column called "year"
year = YEAR([booking date])
Then add "year" in the Legend field and set the data format "mm/dd" for "booking date"
Best Regards
Maggie
Hi v-juanli-msft,
Thank you for your response.
I followed your instructions but something is different with my Power BI.
I added the "year" column to the main table then set the BookingDate data format to "d MMMM" since I can't find the format you suggested on the selection as shown below.
I then add the "BookingDate" as axis and "year" as legend but the axis still shows the complete date even after changing the format as shown below. Also noticed that the BookingDate didn't show the heirarchy under Axis.
I was able to somewhat achieve the desired chart by creating a new column "BookingDate (MMM DD)" which is just equal to "BookingDate" and is formatted to "d MMMM". Also, it shows heirarchy when added as Axis.
The issue with above chart is that it does not show the entire 1 year data, you need to scroll horizontally to view the other data. I want to show the entire 1 year data same as 2nd image above.
Also, there is no option to add a trendline for each year which is important to me.
Additional Notes:
A date filter will be added so the date range can be shorten.
Using "BookingDate (MMM DD)" as filter is not possible since it still contains years data and shortening the range will remove the years that is not part of the range.
Hi Anonymous
I then add the "BookingDate" as axis and "year" as legend but the axis still shows the complete date even after changing the format as shown below. Also noticed that the BookingDate didn't show the heirarchy under Axis.
Click on dwon arrow of the "BookingDate", then you would see two options, "date" and "date hierachy".
As tested, if setting as above, it is not possible to add a trend line.
Best Regards
Maggie
Hi v-juanli-msft,
Thank you for the suggestions.
The date heirarchy has been resolved but my main issue right now is the missing trendline option and the horizontal scrollbar.
It looks like the issue was caused by "Categorical" data type on X-axis but currently it does not allow me to change the data type to "Continuous".
The first approach mentioned above which shows 2017 vs 2018 in full view (without a horizontal scroll bar) and has a trendline is the ideal view but what is missing is the filter functionality.
Hopefully someone could provide a better way to show the desired YoY line chart in full view, with trendline and filter functionality.
Hi Anonymous
How about copying the [date] column which is added to the axis of chart, then add this copied column in the silcer.
Best Regards
Maggie