Requirement: Line chart with Actual + multiple Forecast Versions (dynamic) from different tables

[Ignite190]

Hi everyone,
I’m looking for guidance on a Power BI limitation / workaround for a line chart scenario.

Business requirement

I need to visualize:

• Actual consumption data
• Multiple forecast versions (e.g., V1, V2, Latest, Approved, etc.)
  on the same line chart for comparison over time.

The expected outcome is:

• One line for Actual
• Multiple lines for Forecast, one per Forecast Version
• Forecast Versions are dynamic (not fixed, new ones can appear)
• A Legend driven by Forecast Version showing multiple forecast lines at once

This is a standard forecasting comparison use case.

---

Current data model

• Actual data comes from Table A
• Forecast data comes from Table B
• Forecast table has a column: Forecast Version
• Actual and Forecast are not in the same table
• I already have separate, correct DAX measures:
  • Actual Consumption
  • Forecast Consumption

---

Problem / limitation

• As soon as I put Actual Consumption and Forecast Consumption together in the Y‑axis, Power BI disables the Legend
• Because of this:
  • I cannot place Forecast Version in the Legend
  • I cannot show multiple forecast lines together dynamically
• I understand this is because Power BI doesn’t support Legend + multiple measures in Values
  

  Is there any supported or recommended way in Power BI to achieve this ?

[danextian · Accepted solution]

Hi @Ignite190 

Line charts in Power BI allow either multiple measures (series) as a legend or categories from a column. That said, is to use a disconnected with a column the actual measure name and the forecast version and reference this column in  a measure. Then use the original version column as a slicer.

Please see the attached pbix.

[cengizhanarslan]

Step 1) Create a unified calculated table in DAX

CombinedTable =
UNION (
    SELECTCOLUMNS (
        TableA,
        "Date",   TableA[Date],
        "Value",  TableA[Value],
        "Series", "Actual"
    ),
    SELECTCOLUMNS (
        TableB,
        "Date",   TableB[Date],
        "Value",  TableB[Value],
        "Series", TableB[Forecast Version]
    )
)

Step 2) Create a single measure

Consumption =
SUM ( CombinedTable[Value] )

Step 3) Configure the line chart

• X axis: Date
• Y axis: Consumption
• Legend: Series

Step 4) Connect CombinedTable to your Date table

Modeling tab → Manage Relationships → create a relationship between CombinedTable[Date] and your Date table

[Kedar_Pande]

@Ignite190 

Create Table:

Combined Data = 
UNION(
SELECTCOLUMNS(TableA, "Date", TableA[Date], "Version", "Actual", "Value", TableA[Consumption]),
ADDCOLUMNS(
TableB,
"Version", TableB[Forecast Version],
"Date", TableB[Date],
"Value", TableB[ForecastConsumption]
)
)

Combined[Date] → DateTable[Date]

Measure:

Consumption Value = 
SUM(Combined Data[Value])

Line Chart:

X-axis: Date

Y-axis: Consumption Value

Legend: Version (Actual + all dynamic forecasts)

[MFelix]

Hi @Ignite190 ,

Create a new table with a line saying actuals and the versions fo your forecasts v1, v2 and só on. For this to BE dynamoc you can use Power query or dax.

Then add a new measure similar to this One

Atual + forecasts = 

IF(SELECTEDVALUE(Newtable[columname]), [Actual], CALCULATE([Forecast]), Forecastable[Forecast] = SELECTEDVALUE (Newtable[Columname])))

Then you can use this measure on the values and the column from the new table has Legend.

[grazitti_sapna]

Hi @Ignite190,

CAn be achived by appending both tables together and then using them in Line chart.

I've created a sample .pbix file for you. Hope this is what you require.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!