Combine daily amount only based on Date

[KyawMyoTun]

Dear Experts,
 I have two different tables called Sale and Cost.
But these two tables' data are from different system.
There are no unique ID columns except date.
So, I'd like to combine cost's amount to sale table.
I tried to connect by date table but there are multiple columns in both sale and cost tables.
How can I get correct sale and cost amount by filtering Date table.
My meain purpose is to calculate Margin (Sale - Cost) based on date.
The data are as below
Sale Table

Date	Customer Name	Amount
1/1/2024	AAA co.,ltd	200
1/1/2024	BBB Inc	100
1/2/2024	ABC Ltd	500
1/3/2024	JD	200
1/3/2024	Idea Inc	400
1/3/2024	Jasmine Ltd	700

Cost Table

Date	Supplier Name	Amount
1/1/2024	Super Trading	300
1/2/2024	ARC Mart	500
1/3/2024	TcT Ltd	600
1/3/2024	BNT Co.	700

Thanks and Regards,
KMT

[Kedar_Pande]

@KyawMyoTun 

Create a Date Table:

DateTable = 
ADDCOLUMNS(
CALENDAR(MIN(Sale[Date]), MAX(Cost[Date])),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date])
)

Now, create relationships between the Date table and the Sale and Cost tables.

Total Sales Measure

Total Sales = SUM(Sale[Amount])

Total Cost Measure

Total Cost = SUM(Cost[Amount])

Margin Measure

Margin = [Total Sales] - [Total Cost]

Add a table or matrix visual to your Power BI report.
Add the Date from the Date table to the visual to see data by date.
Add the Total Sales, Total Cost, and Margin measures to the table.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[rajendraongole1 · Accepted solution]

Hi @KyawMyoTun -  create a new Date table that includes all dates covering the range in both your Sale and Cost tables.

Create a relationship between the Date column in the Date Table and the Date column in both the Sale and Cost tables.

Instead of combining the tables directly, you can create measures that calculate the total sale and cost amounts per date using the relationships you set up.

Total Cost Amount = SUM(CostT[Amount])

Total Sales Amount = SUM(SaleST[Amount])

Final output:

Hope this helps

[KyawMyoTun]

Dear @rajendraongole1 ,
 This is working properly. 
Actually I have tried the same but not working before.
I am not sure why happened like that.