DAX Calculate Costs per send using Relationships between two tables

[RPATER]

Hello All,

I have two tables(a Costs table, and a Sends table).  The "Costs" table contains Date, Vendor Name and Cost.  The "Sends" table contains(Date, Vendor Name, # of Sends).  The Cost of sends per month for each vendor differs by Vendor and, in some cases, Month.  I am trying to calculate an "Actual Cost" measure that calculates the cost per send in specific months for each Vendor:

Cost[Vendor Name] = Sends[Vendor Name] && Month(Cost[Date]) = Month(Sends[Date])

Then

Cost[Cost]/Sends[Sends] = Costs per Send

Costs Table Example:

Vendor Name	Date	Cost per Month
Vendor1	1/1/2021	$66,666.67
Vendor1	2/1/2021	$66,666.67
Vendor1	3/1/2021	$66,666.67
Vendor1	4/1/2021	$66,666.67
Vendor1	5/1/2021	$66,666.67
Vendor2	1/1/2021	$1,500
Vendor2	2/1/2021	$1,500
Vendor2	3/1/2021	$1,500
Vendor2	4/1/2021	$1,500
Vendor2	5/1/2021	$1,500
Vendor3	1/1/2021	$90,473
Vendor3	2/1/2021	$19,326
Vendor3	3/1/2021	$13,293
Vendor3	4/1/2021	$13,386
Vendor3	5/1/2021	$18,766
Vendor4	1/1/2021	$0
Vendor4	2/1/2021	$0
Vendor4	3/1/2021	$0
Vendor4	4/1/2021	$0
Vendor4	5/1/2021	$0

Sends Table Example:

DATE	VENDOR NAME	SENDS
4/1/2021	Vendor3	240
4/1/2021	Vendor2	44
4/1/2021	Vendor1	332
5/1/2021	Vendor3	1
5/1/2021	Vendor2	25
5/1/2021	Vendor1	4708
6/1/2021	Vendor3	17
6/1/2021	Vendor2	592
6/1/2021	Vendor1	192

Please let me know if you need anymore information!

Thank You,

Ryan

[amitchandak]

@RPATER , Create a common date table and join both tables with date table and use the measure with date dim or without any common parameter

divide(sum(Cost[Cost])/sum(Sends[Sends))

[RPATER]

Hi @amitchandak ,

I am having trouble making that solution work.  The reason being is I think I need a fixed measure for Cost per send that is set to the specific Vendor and Month.

For Example:

Vendor 1 has 266,974 total sends in April.  The cost for Vendor 1 in April is $66,666

$66,666 / 266,974 is $0.25 per send for Vendor 1 in April.  I would want to use that measure and multiple it by each days sends:

4/1 - 240 sends * $0.25 = $60

4/2 - 332 sends * $0.25 = $83

Currently, with the common date table...the "Cost per send" changes with every date in the table.

Does this make sense?

Thank You,

Ryan