Forum Discussion

arpost's avatar
arpost
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

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

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's avatar
    v-chenwuz-msft
    4 years ago

    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.

1 Reply

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.