Creating a calculated column from 2 tables

[SCNCKS1]

I am hoping someone can help me.

I am trying to create a calculated column in TBL_PROD that sums the totals in the amount column from TBL_CT based on the date and project number that will then add it to the TBL_PROD so it only shows the total value regardless of how many date entries are in TBL_PROD.  The cost tracking total variable below works, but it adds the same value (total of the amount column for the day) under each date. My thought was to divide by the total number of entries for the date, but the count rows formula is counting everything on the date, not just the entries for the project so my number is way too small. There is no relationship between the tables.

Project Cost =

VAR COST_TRACKING_TOTAL =

SUMX(

    FILTER(TBL_CT,

    TBL_CT[EffectiveDate] = TBL_PROD[WorkDate] && TBL_CT[ProjectIDId] = TBL_PROD[ProjectIDId] ) , TBL_CT[Amount])

VAR COUNT_COST_TRACKING = CALCULATE(COUNTROWS(TBL_PROD),FILTER(TBL_PROD,TBL_PROD[WorkDate] =  EARLIER(TBL_PROD[WorkDate])))

RETURN

COST_TRACKING_TOTAL

Original Data

EffectiveDate	Amount
10/28/2022	$45.93
10/28/2022	$67.77
10/28/2022	$240.00
10/28/2022	$250.00
10/28/2022	$1,360.00
10/28/2022	$560.00
10/28/2022	$100.00
10/28/2022	$450.00
11/8/2022	$520.00
11/8/2022	$1,150.00
11/8/2022	$17,337.54
11/8/2022	$17,134.91
11/8/2022	$236.25
11/8/2022	$327.61
12/1/2022	$354.60
12/1/2022	$44.00
12/2/2022	$105.68

[lbendlin · Accepted solution]

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.