Look up first purchase date and adding total from two tables

[leilei787]

Hi All, i have two questions, wonder if someone could help please!

i have two data tables. one contains orders that before year of 2020, another table contains orders that after 2020.

The table 1

Cust ID	Order Date	Product	Qty
100	1/1/2015	A	1
200	2/4/2014	C	3
300	5/10/2018	A	2
400	6/4/2016	C	5
500	7/3/2018	A	6
600	4/6/2019	A	32
100	2/4/2012	BB	5
200	5/2/2011	A	3
300	6/3/2018	C	6
400	4/3/2019	BB	8
500	9/3/2011	A	4
600	6/5/2017	A	2

The table 2

Cust ID	Order Date	Product	Qty
100	4/2/2020	A	4
200	5/3/2021	BB	2
300	2/2/2022	A	5
400	2/6/2020	A	6
500	4/5/2020	BB	2
600	8/1/2021	BB	2
700	1/1/2021	A	1
800	3/1/2022	A	4

i want to do the following

1) look up the first purchase data across two tables. for example, for product A, cust ID 100 first purchase date was 1/1/2015; for Cust ID 700, the first purchase date was 1/1/2021...for product BB, Cust ID 200 First purchase date was 5/3/2021.....basically to check two table and find the first purchase date

2) sum up total unit of product from two tables...for example, total units of product A that customer ID 100 purchased = 5 (1 from table A and 4 from table B)

thank you all!!

[Anonymous · Accepted solution]

Hi @leilei787 ,

Create a calculated table.

Table3 = UNION(Table1,Table2)

Then create measures.

_first = CALCULATE(MIN(Table3[Order Date]),ALLEXCEPT(Table3,Table3[Cust ID],Table3[Product]))

_sum = CALCULATE(SUM(Table3[Qty]),ALLEXCEPT(Table3,Table3[Cust ID],Table3[Product]))

Best Regards,

Jay

[amitchandak]

@leilei787 , One you you append these two table in power Query

Append : https://radacad.com/append-vs-merge-in-power-bi-and-power-query

The second is create common dimensions for Date, product, Customer ID and join with both table

Create a combine measure and analyze using common tables

Sum(Table1[Qty]) + Table2[Qty]

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.