Retrieve data in measure from different tables based on date

[awitt1Temp]

I am looking to write a single measure that adds a sales + forecast value for a give month. To do this I have to sum data in a sales table for dates today and earlier with the forecasted amount for future dates that live in a different forecast table. 

As a result, prior months would just add up actuals and future months would only sum the forecasted amount. For current month though we would have actuals up through yesterday technically and forecast from today on.

For this month, the result should be $253,500 ($241,200 in actuals from 9/1 - 9/18 & $12,300 in forecast from 9/19 - 9/30)

Date	Actual Sales
1-Sep	10000
2-Sep	$        30,000
3-Sep	$        25,000
4-Sep	$        10,000
5-Sep	$          9,500
6-Sep	$              500
7-Sep	$              600
8-Sep	$        15,000
9-Sep	$        10,000
10-Sep	$        30,000
11-Sep	$        25,000
12-Sep	$        10,000
13-Sep	$          9,500
14-Sep	$              500
15-Sep	$              600
16-Sep	$        15,000
17-Sep	$        10,000
18-Sep	$        30,000
19-Sep
20-Sep
21-Sep
22-Sep
23-Sep
24-Sep
25-Sep
26-Sep
27-Sep
28-Sep
29-Sep
30-Sep

Date	Forecast
1-Sep	$           950
2-Sep	$           950
3-Sep	$           950
4-Sep	$        1,050
5-Sep	$        1,050
6-Sep	$        1,050
7-Sep	$        1,150
8-Sep	$        1,150
9-Sep	$        1,150
10-Sep	$           950
11-Sep	$           950
12-Sep	$           950
13-Sep	$           950
14-Sep	$           950
15-Sep	$           950
16-Sep	$        1,050
17-Sep	$        1,050
18-Sep	$        1,050
19-Sep	$        1,150
20-Sep	$        1,150
21-Sep	$        1,150
22-Sep	$           950
23-Sep	$           950
24-Sep	$           950
25-Sep	$           950
26-Sep	$           950
27-Sep	$           950
28-Sep	$        1,050
29-Sep	$        1,050
30-Sep	$        1,050

[v-henryk-mstf]

Hi @awitt1Temp ,

Whether the advice given by @DataInsights  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[DataInsights · Accepted solution]

@awitt1Temp,

Try this solution.

1. Create a date table with relationships to each table:

2. Create measures:

Actual Sales = SUM ( Actual[Actual Sales] )

Forecast = SUM ( Forecast[Forecast] )

Blended Amount = 
VAR vActualEndDate =
    CALCULATE ( MAX ( Actual[Date] ), ALL ( 'Date' ) )
VAR vForecast =
    CALCULATE ( [Forecast], KEEPFILTERS ( 'Date'[Date] > vActualEndDate ) )
VAR vResult = [Actual Sales] + vForecast
RETURN
    vResult

3. In a table visual, add Date (must be from Date table) and [Blended Amount]: