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...
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

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/