Retrieve data in measure from different tables based on date

Question

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.&nbsp;&nbsp;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.&nbsp;For this month, the result should be $253,500 ($241,200 in actuals from 9/1 - 9/18 &amp; $12,300 in forecast from 9/19 - 9/30)&nbsp;DateActual Sales1-Sep100002-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30,0003-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,0004-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,0005-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9,5006-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5007-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6008-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15,0009-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,00010-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30,00011-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,00012-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,00013-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9,50014-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50015-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 60016-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15,00017-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,00018-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30,00019-Sep&nbsp;20-Sep&nbsp;21-Sep&nbsp;22-Sep&nbsp;23-Sep&nbsp;24-Sep&nbsp;25-Sep&nbsp;26-Sep&nbsp;27-Sep&nbsp;28-Sep&nbsp;29-Sep&nbsp;30-Sep&nbsp;&nbsp;DateForecast1-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9502-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9503-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9504-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,0505-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,0506-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,0507-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,1508-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,1509-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,15010-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 95011-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 95012-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 95013-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 95014-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 95015-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 95016-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,05017-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,05018-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,05019-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,15020-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,15021-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,15022-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 95023-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 95024-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 95025-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 95026-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 95027-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 95028-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,05029-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,05030-Sep&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,050&nbsp;&nbsp;

datainsights · Accepted Answer

awitt1Temp,
&nbsp;
Try this solution.
&nbsp;
1. Create a date table with relationships to each table:
&nbsp;

&nbsp;
2. Create measures:
&nbsp;
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] &gt; vActualEndDate ) )
VAR vResult = [Actual Sales] + vForecast
RETURN
    vResult
&nbsp;
3. In a table visual, add Date (must be from Date table) and [Blended Amount]:
&nbsp;

&nbsp;

Forum Discussion

Retrieve data in measure from different tables based on date

2 Replies

Related content

Cumulative Difference from Another measure

Retrieving data via HTML

Cannot retrieve Contacts from API

Retrieving deleted notebook

Different schema into different destination

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