Combine selected values from different columns into one column

[ClaireN]

Hi PBI people

My boss has come up with a method for forecasting that he wants in PBI (lets not question its logic lol).

I have a SQL query that gives me the daily sales at whatever time of the day it is refreshed (this value will increase as the day progresses). Plus whats know as preinvoice revenue, which is sort of a prediction of what $$ will be coming in the future (ignore the fact that sales actual and preinvoice in the past don't really match up - thats some wizardness in the system only a few understand).

I need to take all the values from the daily sales column (except the current day, as not all sales will be in the system) and two values from the preinvoice column and combine them into one column.

Is this possible in DAX? 

Say today is 13/11/20 and I need to run the forecast.  In the table below I've purposely ignored the $25,000 in the daily sales column (as that is todays date where I am and an incomplete day of sales) and taken the Preinvoice value from the 13/11/20 and 14/11/20 into my "Sales Actual + 2 days preinvoice" column. 

Can I automate this? Any ideas - I don't any know R 😞

I've then got to make a cumulative plot of "sales actual 2020" (excluding the current day) and a cumulative plot of "sales actual + 2 days preinvoice" and find the equation of the line to then predict what the sales will be at the end of the month (line is forced through zero, so in excel its something like y = 154966x. I found a youtube video that may be able to help with that part but I have no idea how to make the combined column (sales actual + 2 days pre invoice). 

Much appreciate any attempts at this!

Date	Sales Actual 2020	PreInvoice Future test revenue	Sales Actual + 2 days Preinvoice
1/11/2020	$126,000	$170,123	$126,000
2/11/2020	$201,456	$133,456	$201,456
3/11/2020	$81,563	$71,122	$81,563
4/11/2020	$190,552	$143,450	$190,552
5/11/2020	$204,222	$193,620	$204,222
6/11/2020	$153,456	$176,187	$153,456
7/11/2020	$123,456	$174,182	$123,456
8/11/2020	$87,000	$151,908	$87,000
9/11/2020	$225,000	$128,532	$225,000
10/11/2020	$123,456	$131,458	$123,456
11/11/2020	$225,010	$148,834	$225,010
12/11/2020	$159,123	$172,066	$159,123
13/11/2020	$25,000	$210,258	$210,258
14/11/2020		$140,957	$140,957
15/11/2020		$120,627

[ClaireN · Accepted solution]

Thanks so much for your response it didn't quite work but was super close!, I ended up getting additional help and this is what worked - will post here in case anyone needs to do this: 

the measure [today] = TODAY( )

Combined Actual Sales and PreInvoice =
VAR mySales =
CALCULATE( [Sales Actual 2020],
FILTER( ALLSELECTED( Dates[Date] ),
Dates[Date] < [Today]
),
VALUES( Dates[Date] )
)
VAR myPreInv =
CALCULATE( [PreInvoice Future test revenue],
FILTER( ALLSELECTED( Dates[Date] ),
Dates[Date] >= [Today] &&
Dates[Date] < [Today] +2
),
VALUES( Dates[Date] )
)
RETURN
mySales + myPreInv

[amitchandak]

@ClaireN , Not very clear. But something like this should work

Cumm Sales =
var _max = maxx(allselected(Sales),sales[date])
return
CALCULATE(SUM(Sales[Sales Actual 2020]),filter(allselected(Sales),Sales[date] <=max(Sales[date]) && Sales[Sales] <=_max))
+ CALCULATE(SUM( PreInvoice Future test]),filter(allselected(Sales),Sales[date] <=max(Sales[date]) && Sales[Sales] <=_max +2 && Sales[Sales] >_max))