Date Spread Measure

[jcawley]

Hello all,

My goal is to have a measure that sums payment amounts based on the spread of two dates, across two different tables.

Purchase table:

Customer ID	Purchase Date
555	1/1/2022
444	1/1/2022
999	6/6/2021

Payments Table:

Customer ID	Payment Date	Payment Amount
555	1/30/2022	$        100.00
444	1/10/2022	$        100.00
999	1/10/2022	$        100.00

The desired output would be if the payment date is no greater than 30 days after the purchase date sum payment amounts.

So the measure in question should spit out "$200" in this example.

I know I could get this working making a calculated column for the date spread, but I'm trying to avoid doing so. 

Any help would be appreciated!

[VahidDM]

Hi @jcawley 

Try this measure:

Date Spread =
VAR _A =
    ADDCOLUMNS (
        ADDCOLUMNS (
            'Payments Table',
            "Purch", RELATED ( 'Purchase table'[Purchase Date] )
        ),
        "DateDiff", DATEDIFF ( [Purch], [Payment Date], DAY )
    )
VAR _B =
    FILTER ( _A, [DateDiff] < 30 )
RETURN
    SUMX ( _B, [Payment Amount] )

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[tackytechtom · Accepted solution]

Hi @jcawley ,

I think I got to a solution:

I pressumed there is a one to many relationship from purchase to payments. I also reckon that the key to create the connection on is not Customer but maybe Order? Yet, I joined on Customer anyway for the sake of this example 🙂 :

Here is the measure I created:

TomsPaymentMeasure = 
CALCULATE ( 
    SUMX ( Table16b, [Payment Amount]),
    FILTER ( Table16b, Table16b[Payment Date] -30 < RELATED ( Table16a[Purchase Date] ) )
)

Hope this helps! 🙂

/Tom
https://www.tackytech.blog

https://www.instagram.com/tackytechtom