Connecting lines between a point from the line chart to a point on the error bar

[deepanwita25]

I have created a graph like this. The slicers represent Country/Product/Measure chosen.

The light blue line in the graph depicts Actual value. The light blue dotted line depicts the value forecasted.

The grey markers depicted with error bar shows predicted values at that time point. Eg. March 2024 has a blue dot which is ACtual and a grey dot which is Predicted for March 2024. There is also a slicer called "Time Ahead from the Last Data Available". It denotes in this case, March 2024 was predicted 2 months ago in Jan 2024.

Similarly for other points, Nov 2024 (grey marker value) was predicted 2 months ago in Sep 2024. Is it possible to do something like shown below. If yes, how, can someone help me please:

[deepanwita25]

Hi I have created the table as suggested

I have the common Month&Year
Value represents the Actual Values
Predicted_1M Prior represents the corresponding Predicted values for that month
As you suggested, I have created a linking column called Date_1M Prior which is Date-1 month for me for Predicted_1M Prior col..It signifies 1 month ago. Feb 2024 was predicted in Jan 2024 and the predicted value is given by Predicted_1M Prior.

After this I cant follow how to create a link between ACtual and Predicted Values. Can you help me here please?

[deepanwita25]

I could just create 2 line chart based on the data in the new table I posted above and it looks like this:

How do I connect these 2 lines

[deepanwita25]

Hello, Thank you for you solution. I am trying it and will let you know if this works!

[freginier]

Hey there!

If I understand correctly, you want to connect the actual value (blue dot) to the predicted value (grey dot with error bar) in the line chart. Currently, the actual and forecasted values are plotted separately, but they need a visual connection.

Power BI does not natively support connecting separate data points directly with custom lines, but we can use a workaround:

1. Create a new table that contains Actual MonthYear, Forecast MonthYear, and the Actual/Forecast values in the same row.( This ensures that each actual value has a corresponding predicted value.)

2. Add a calculated column to link the actual and forecasted values by MonthYear. Example: PredictionDate = ActualDate + TimeAheadMonths

3. Scatterplot for actual values (blue dots). Line chart for forecasted values. Also ensure that both are using the same X-axis (MonthYear).

This will visually connect actual and forecasted values but will not be a "true" connection line.

If this doesn't work, you can also use a DAX formula. 

LineConnector =
VAR ActualValue = SELECTEDVALUE( ActualsTable[ActualValue] )
VAR ForecastValue = SELECTEDVALUE( ForecastTable[PredictedValue] )

RETURN
IF(
NOT(ISBLANK(ActualValue)) && NOT(ISBLANK(ForecastValue)),
(ActualValue + ForecastValue) / 2, // Find midpoint for a smooth connection
BLANK()
)

This creates a dynamic "midpoint" that can be plotted to simulate a connection between the two.

Hope this helps!

😁😁