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
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 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?
Solved! Go to Solution.
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
108 | |
108 | |
90 | |
61 |
User | Count |
---|---|
171 | |
138 | |
132 | |
102 | |
86 |