Forum Discussion

jcawley's avatar
jcawley
Helper III
4 years ago
Solved

Date Spread Measure

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!

Need Help
  • tackytechtom's avatar
    tackytechtom
    4 years ago

    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

2 Replies

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    4 years ago

    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

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    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/