Forecast and Actuals in one line chart Data model with both relationship

[Zuz]

Hi all,

I'm trying to create line chart with forecast and actual data in one line. This is my data model:

I have two filters:

1. Last X days related only to one chart

2. Months with Total - related only to a column chart with 3 measures (therefore I have 2 tables of consumption and forecast)

Now I am trying to create a line chart but I have tried removefilters, userelationship, and filter on both tables but I have only the first 4 months' data. I want one column with all months with forecast and another with actuals (based on data).  

Incorrect totals for months:

Can somebody please help me create the right measures for Forecasts and Actuals? Thank you in advance. 

[amitchandak]

@Zuz , seem like data is coming from consumption and forecast which are not joined with the Calendar table. It should come from the calendar(2) and forecast(2) . Or you should join the table consumption and forecast with date table. Also, they should not join with each other. they should with common tables

[Zuz]

I switch the tables and now it returns values only for the first 4 months. 

I have two tables due to the column chart. The column chart shows the right values. 

There are these measures:

Total by Month AY = VAR _Total = CALCULATE(SUM('Consumption (2)'[suma_odpoctov_kwh]),ALL(Months_with_Total), 'Consumption (2)'[Year]= 2023) RETURN SWITCH( SELECTEDVALUE('Months_with_Total'[Month Name]), "Total", _Total, CALCULATE( SUM('Consumption (2)'[suma_odpoctov_kwh]), 'Consumption (2)'[Year]= 2023))

Total by Month PY = VAR _Total = CALCULATE(SUM('Consumption (2)'[suma_odpoctov_kwh]),ALL(Months_with_Total), 'Consumption (2)'[Year] = 2022) RETURN SWITCH( SELECTEDVALUE('Months_with_Total'[Month Name]), "Total", _Total, CALCULATE( SUM('Consumption (2)'[suma_odpoctov_kwh]), 'Consumption (2)'[Year] = 2022))

Directly in the table Consumption (2). 

In the Forecast (2) table is this measure:

Total FC by Month 2 = VAR _Total = CALCULATE(SUM('Forecast (2)'[Spotreba_kWh]),ALL(Months_with_Total)) return SWITCH( SELECTEDVALUE('Months_with_Total'[Month Name]), "Total", _Total, CALCULATE(SUM('Forecast (2)'[Spotreba_kWh]))) 

I tried to use both tables for the line chart but had no success. The month's slicer from the Calendar table is inactive for the line chart (I want to have the whole year) and active for the column chart.