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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
AbigailCole
Frequent Visitor

Multi-Year Line and Clustered Column Chart with Trend Line

Hello all,

 

My boss's boss is asking me to make this chart from excel in power bi:

AbigailCole_0-1692105892852.png

 

However, I can only get power bi to show me trend lines (the dashed ones on the chart) when I use year or the full date as the x-axis. aka it will not show the trend line when using month/year as the x-axis. Googling this gives me old versions of power bi or only one year trend lines, and he wants up to 5 years, so any help would be appreciated!

 

I have thought about calculating the trend line myself however there isn't a place to add in a line (only error bars) when I use the month/year as my x-axis.

 

I have a date table that lets me choose date, year, month, ect. Here is a sample set of the data. Orange is a count from one table, green and blue are measures.

 

OrangeGreenBlue LineMonthYear
17083.702.03Nov2020
18284.252.16Dec2020
17885.602.08Jan2021
17077.132.20Feb2021
20793.342.22Mar2021
21487.522.45Apr2021
13387.921.51May2021
15888.901.78Jun2021
12186.701.40Jul2021
9185.931.06Aug2021
8085.710.93Sep2021
11291.801.22Oct2021
11288.151.27Nov2021
8785.121.02Dec2021
8188.900.91Jan2022
9788.031.10Feb2022

 

 

Honestly I think he would be okay with the trend lines on a seperate chart if it at least could still show it on a monthly axis. so any suggestions like that would be great too!

1 REPLY 1
amitchandak
Super User
Super User

@AbigailCole , Based on what I get for the above Line clustered bar visual you need Year and Month Axis with concatenate label-off, sorted on axis.

 

keep the measures in y-axis and line as per need. That is best I can think of in standard power bi visuals

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.