Actuals vs Forecast in one line in the line chart.

[ROG]

Hi guys,

I have the table below and I would like to have a line chart similar to the chart below, showing the actual numbers until June 2022, and from July to December showing the forecast.

Month Date comes from Calendar table.

Actuals from the FACT Table Actuals

Forecast from Forecast table.

I know it's wrong, but this is the logic I have in mind. 

Actuals and FRCST = IF('Forecast (2022)'[MonthEnd] < JUNE , [Actuals], [Forecast_Amount])
I aslo tried this measure from SQLBI, but it won'r work for me as always will have actuals, and I need to show forecast from July to Dec 2022 :
Remaing Forecast = VAR LastDateActuals = calulate (MAX ( Actuals [Date_Key]), REMOVEFILTERS () )
RETURN LastDateActuals.

I'd greatly apprecite your help on that.

Thank you.

[m_alireza]

Hi @ROG,
Could you send me a sample pbix file with dummy data? That will help me understand the columns in your tables and relationships so I can come up with a solution there for you to implement.

[m_alireza · Accepted solution]

Hi @ROG ,

Try creating two measure to dictate whether it should take Actuals or Forecast and then adding them to your Y axis in the line chart. You will then get two line colours. I assume you have a Date column in your Calendar table. 

Actual Measure:

Actual = 

CALCULATE(SUM('ACtual Table'[Actual]),'Actual Table'[Date]=MAX('Calendar Table'[Date]),
'Calendar Table'[Date]<DATE(2022,07,01))

Forecast Measure: 

Forecast = 

CALCULATE(SUM('Forecast Table'[Forecast]),'Forecast Table'[Date]=MAX('Calendar Table'[Date]),
'Calendar Table'[Date]>DATE(2022,06,01) && 'Calendar Table'[Date]<=DATE(2022,12,31))

Only issue is if you put them in the graph, the lines will be disconnected

If you want the two lines to connect, you can extend the Actual to include the Forecast for July. If you want to do that, then replace the Actual Measure with this:

Actual with July Forecast= 

VAR _Julyforecast=MAX('Calendar Table'[Date])=DATE(2022,07,01) && MAX('Calendar Table'[Date])<=DATE(2022,07,31)
VAR _UntilJune22=MAX('Calendar Table'[Date])<DATE(2022,07,01)

RETURN

IF(_Julyforecast , CALCULATE(SUM('Forecast Table'[Forecast]),'Forecast Table'[Date]=MAX('Calendar Table'[Date])),


IF(_UntilJune22,CALCULATE(SUM('Actual Table'[Actual]),'Actual Table'[Date]=MAX('Calendar Table'[Date]))))