Need Help with DAX formula to SUM Values Based on Month in another Table

[Vroom_Master3]

I have two tables, One for veryifying data agaisnt a target, and one for tracking the many lines of Cost entrires. 

Neither table has a specific way I can relate them as you can see below. 

TableA 

Date    |    Total_Cost    |    Location

Nov 1                     350                   A

Nov 6                     120                   A

Nov 6                     100                   B

Nov 6                     200                   C

Nov 12                    30                    A

...

Dates Repeat, Cost varies, and Location is either A,B, or C

TableB

MonthStart    |    MonthEnd    |    CostA    |    CostB    |    CostC

Nov 1, 23              Nov 30, 23             500              100         200

Dec 1, 23               Dec 31, 23                    ....

I need to be able to represent All costs for each location based off of a date range, I can't get around the usage of the date range in TableB. If i need to make more than 1 column that is super fine, but I cant figure out a way to compare the Dates in Table A and the Location and be able to acutally reference it. Every combination of TREATAS and CALCULATE i've thought of wont do it so I'm hoping you guys can help. 

[Anonymous · Accepted solution]

Hi @Vroom_Master3 ,

I'll show you my test data first, the date column data types in Table A and Table B are Date:

Then you can create three new columns with the following DAX:

CostA = 
CALCULATE(
    SUM(TableA[Total_Cost]),
    FILTER(
        'TableA',
        'TableA'[Date] >= 'TableB'[MonthStart] && 'TableA'[Date] <= 'TableB'[MonthEnd] && 'TableA'[Location] = "A"
    )
)

CostB = 
CALCULATE(
    SUM(TableA[Total_Cost]),
    FILTER(
        'TableA',
        'TableA'[Date] >= 'TableB'[MonthStart] && 'TableA'[Date] <= 'TableB'[MonthEnd] && 'TableA'[Location] = "B"
    )
)

CostC = 
CALCULATE(
    SUM(TableA[Total_Cost]),
    FILTER(
        'TableA',
        'TableA'[Date] >= 'TableB'[MonthStart] && 'TableA'[Date] <= 'TableB'[MonthEnd] && 'TableA'[Location] = "C"
    )
)

The final result is as follows:

Best Regards,

Dino Tao

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