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

arpost — Wed, 07 Sep 2022 15:00:05 GMT

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?

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

v-chenwuz-msft — Thu, 08 Sep 2022 06:53:32 GMT

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.

topic Re: Help! Anyone have a DAX solution for summing values based on dates in a different table? in DAX Commands and Tips

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

Scenario

Desired Result

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