Monthly column chart combines 3 different tables (1 x monthly values, 2 x daily values)

[Pete230]

Hi together,

I would like to create a column chart showing the months of the current year with 3 valuetypes from 3 tables combined:

1. column: Energy (monthly values) -> time stamp column (01.01.2023) for January
2. column: Energy (daily values)       -> time stamp column (01.01.2023, 02.01.2023 ...) for January
3. column: Energy Prognosis (daily values) -> time stamp column (01.01.2023, 02.01.2023 ...) for January

The graph could look like this and cosists out of the following rules:

Starting point is the current date, e.g. 19.04.2023, the dax formula (or even possible in power querry?) shoul do the following:

• all past months energy has to be taken from the 1. table (Energy monthly values), here it would be  Jan - March (marked in yellow in the screenshot)
• The current month (april) should use all energy values until today (table 2, daily energy values) (marked in red in the screenshot) and filled up by the prognosis values (table 3, energy prognosis daily data) (marked in blue in the screenshot)
• The coming months should display just the sum of the daily energy prognosis values (table 3) (marked black in the screenshot)

Every day the sum should roll, take the real energy value of yesterday and take one day less of the prognosis values.

All the time stamp columns of the tables are connected with one calandar table, so mixing daily and monthly values works.

I hope the problem is described to be unterstood. Does anyone of you know how to do that either in dax, but preferably in power querry.

Cheers

Pete