Sum Sales over two unrelated tables

[anktaggrwl]

I have two tables in my data model that are not linked.

In Table 1 - I have a table that shows committed but not actual sales - i.e. these are sales opportunities that a client has told us we have 'won' but the actual sale has not yet occured - i.e. money has not actully been given to us yet.

Product	Commitment Date	Committed Sales
Product A	01/24/2012	$20
Product A	03/04/2018	$100
Product B	05/02/2018	$40
Product C	02/03/2018	$30

Table 2 is a table of all actual sales across the region - regardless of if it shows up in our commited sales table - we can have many products that are sold across multiple years - and this is a table that shows monthly sales by product.

Product	Sale Date	Sales Amount
Product A	12/31/2015	$5
Product A	01/31/2016	$10
Product A	05/31/2018	$20
Product B	06/30/2018	$18
Product B	02/28/2018	$36
Product C	03/31/2018	$4
Product D	06/30/2010	$1
Product E	10/31/208	$40

I want to track the amount of actual sales from Table 2, but only sum those that have occured after the 'Commitment Date' in Table 1 for the corresponding product - and it's ok to have things be double counted - like in the case of Product A in Table 1 - For the commitment date of '01/24/2012' i would have a sum of the first three lines of Table 2, but for the next commitment date in Table 1 for Product A - I would only have the sum of line 3 in Table 2 (since that is the only sale that occured after '3/4/2018').

I have an Actual Sales measure:

Actual Sales = Sum[Sales Amount]

but since my tables are not linked, and cannot be - I'm not sure how to create a calculated column or measure that will give me the right amounts in Table 1.

Secondarily - I'd love to be able to break out the actual sales for every line in Table 1 - so the first line would show sales in 2015, 2016, and 2018, while the second line would show sales in 2018 only (all of which occured only after the 'commitment date' in any given year).

Is this possible to do without creating a relationship between the two tables via a new calculated table or adding calculated columns that would do this?

Ideal result would be a table that does this:

Product	Commitment Date	Committed Sales	Actual Sales
Product A	01/24/2012	$20	$35
Product A	03/04/2018	$100	$20
Product B	05/02/2018	$40	$18
Product C	02/03/2018	$30	$4

and bonus awesome would be a result that does this:

Product	Commitment Date	Committed Sales	CY 2015 Actual Sales	CY 2016 Actual Sales	CY 2017 Actual Sales	CY 2018 Actual Sales
Product A	01/24/2012	$20	$5	10	0	20
Product A	03/04/2018	$100	$0	0	0	20
Product B	05/02/2018	$40	$0	0	0	18
Product C	02/03/2018	$30	$0	0	0	4

Thanks!

[anktaggrwl]

I think i'm getting closer - but need help with the date portion now.

So what I decided to do was create a measure as such:

Actual Sales = 
CALCULATE(Sum([Sales Amount]),
    TREATAS(VALUES(Table1[Product]),Table2[Product]))

Which give me a summation of sales amount for every product in Table 1 - but now I don't know how to sum the sales amount values in Table2 only if Table1[Product]= Table2[Product] AND where the corresponding Table1[Commitment Date]<=Table2[Sale Date] for each Product in Table 1.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution from this file.

Hope this helps.