Merge two tables with only dates in common, but table1 have 2 values on one and one value on table2

[dottoreh2]

I have two tables, the first looks something like this

Date	Amount	Store
12/26/2022	$35,833.12	1
1/16/2023	$13,282.46	2
1/16/2023	$62,436.07	1
1/17/2023	$11,978.66	2
1/17/2023	$114,619.78	1

I also have another table with the data from another store taht looks like this

Date	Amount on Store 3
02/01/2024	$46,592.26
03/01/2024	$12,589.58
04/01/2024	$11,753.65
05/01/2024	$17,233.93
08/01/2024	$33,758.32

I've tried to merge both tables but I end up with duplicate values on store 3, because I have two dates on table 1.

How can I merge the tables or get a calculated value that sums up all 3 stores?

[v-jianpeng-msft · Accepted solution]

Your solution is great @audreygerred 

Hi, @dottoreh2 

If you don't merge your tables, your DAX expression might look something like this:

Amount store12 =
SUMX ( ALL ( 'Table' ), 'Table'[Amount] )

Amount store3 = SUMX(ALL(Table1),'Table1'[Amount on Store 3])

Amount123 = [Amount store12] + [Amount store3] 

Create the above three measures and put them into the card visual, and the result is as follows:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[audreygerred]

Hi! You can go to Power Query, go to your second table (the one with Amount on Store 3), rename Amount on Store 3 to Amount so that it aligns with the naming of the first table. Then, add a custom column named Store and have the value be 3. Now your two tables have the same columns. Next, click the down arrow on Append Queries, select Append Queries as New select your two tables to append and click OK. They are now one table. Finally, right click on both of the individual queries of your two separate tables and uncheck 'Enable load'. This way, only the appended table appears in Power BI. Once you have that, you can make a measure for the sum = SUM('YourTable'[Amount]).