Forum Discussion

Gerbil's avatar
Gerbil
Icon for Helper I rankHelper I
2 years ago
Solved

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:   Each point represents an average of all the datapoints for that year. However, I only have datapoint...
  • Trevor_G's avatar
    2 years ago

    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: 

     

    Here is the measure to combine them into one line: 

     

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

     

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

     

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

    Let me know if this works for you.