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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Gerbil
Helper I
Helper I

How to make a line chart with points from different tables?

I am trying to make a line chart of averages from the past 5 years, something like this:

Gerbil_0-1717532365565.png

 

Each point represents an average of all the datapoints for that year. However, I only have datapoints for the past two years (2023 and 2022). But I have historical information on what the values are for the previous years, from 2018 to 2021. So it's a separate table with static values. How do I create a line chart with a single line connecting all the averages from 2018 to 2023?

1 ACCEPTED SOLUTION
Trevor_G
Frequent Visitor

You can (and should) utilize a 'Date Table' for this. 

The two tables are connected through the common date table.

 

The x axis of your chart is this date table. 

 

Here is an article on creating and using a date table: 

https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables 

 

Once this is setup and connected, you can use a measure to combine both values into a single line. 

 

Here are the results: 

Mixed_Table_Results.png

 

Here is the measure to combine them into one line: 

Mixed_Table_Measure.png

 

Here is the data model (using the 'Date Table' as the common connection: 

Mixed_Table_Model.png

 

Here is a sample of the Date Table (done in excel): 

Mixed_Table_Date_Sample.png

 

Here is the sample data (one data point for historical, and multiple for data points for 2022-2023):

Mixed_Table_SampleData.png

Let me know if this works for you. 

View solution in original post

1 REPLY 1
Trevor_G
Frequent Visitor

You can (and should) utilize a 'Date Table' for this. 

The two tables are connected through the common date table.

 

The x axis of your chart is this date table. 

 

Here is an article on creating and using a date table: 

https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables 

 

Once this is setup and connected, you can use a measure to combine both values into a single line. 

 

Here are the results: 

Mixed_Table_Results.png

 

Here is the measure to combine them into one line: 

Mixed_Table_Measure.png

 

Here is the data model (using the 'Date Table' as the common connection: 

Mixed_Table_Model.png

 

Here is a sample of the Date Table (done in excel): 

Mixed_Table_Date_Sample.png

 

Here is the sample data (one data point for historical, and multiple for data points for 2022-2023):

Mixed_Table_SampleData.png

Let me know if this works for you. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.