Help! Anyone have a DAX solution for summing values based on dates in a different table?

[arpost]

Greetings, community! Hope someone can help me figure out this DAX scenario. I've tried just about everything of which I can think (SUMX, Calculate, SelectedValue, etc.), and can't figure out a solution. It seems like this should be simple, but it is driving me crazy! Let me set the stage with a "simplified" example.

Scenario

I have a matrix with a date from one table and need to show the total $ in that window of time in another table.

Here's the sample data from the three tables:

And here is the data model:

Desired Result

What I'm hoping to achieve is that, when I single-select slice to a specific category, the matrix will show the total $ that was paid based on each account's Start/End Date range. So, based on the data, New Accounts that had a StartDate in June would show $3000 for New Accounts.

When sliced to Returning Accounts, it would show June with $600 (All Payments for Account #1 >= StartDate 6/21) and August with -$1500.

Does anyone have a brilliant idea or suggestion?

[v-chenwuz-msft · Accepted solution]

Hi @arpost ,

Measure:

Measure =
VAR _s =
    SUMMARIZE (
        'DateSummary',
        [AcountKey],
        [StartDate],
        [EndDate],
        "amount",
            CALCULATE (
                SUM ( OrderSummary[Amount] ),
                FILTER (
                    OrderSummary,
                    [AccountKey] = DateSummary[AcountKey]
                        && [TransactionDate] >= [StartDate]
                        && IF ( ISBLANK ( [EndDate] ), TRUE (), [TransactionDate] <= [EndDate] )
                )
            )
    )
RETURN
    SUMX (
        _s,
        [amount]
    )

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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