Forum Discussion

dw700d's avatar
dw700d
Post Patron
5 years ago
Solved

Calculating date difference

I’m not sure if this is possible in power bi. But if it is I could use some guidance. I am trying to calculate the difference between two dates. The first date is a purchase date. The second date is a payment date. Purchase dates have one date per order number. Payment dates can have one date per order number or multiple dates per order number. For example, on 1/1/2020 I purchased an item and then I made payments  on three separate occasions in March, April and May. How do I calculate the difference between the purchase date and the most recent payment date?

 

 

Order numberpurchase datecost
11/1/2020300
21/2/2020400
31/3/2020100
41/4/2020500
51/5/2020900
61/6/2020250
71/7/2020100
81/8/2020400
91/9/2020100
101/10/2020150
111/11/2020200
121/12/2020300

 

Order numberPayment Date                 cost         
13/1/2020100
14/5/2020100
15/1/2020100
22/2/2020400
43/2/2020300
43/2/2020100
43/2/2020100
63/22/2020150
74/1/202050
74/2/202050
85/2/2020200
85/8/2020200
Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi dw700d,

     

    Try measure as:

    Measure =

var _date1=

CALCULATE(

    MAX(Table1[purchase date]),

    FILTER(

        Table1,

        Table1[Order number]=MAX(Table2[Order number])

    )

)

var _date2=

CALCULATE(

    MAX(Table2[Payment Date                 ]),

    FILTER(

        ALL(Table2),

        Table2[Order number]=MAX(Table2[Order number])))

return

DATEDIFF(_date1,_date2,DAY)

    Here is the output:

     

    Here is the demo, please try it: Calculating date difference

    Best Regards,

    Link

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    dw700d add the following column, assuming these two tables are linked on Order Number

     

    
Days = 
VAR __pmtTable = CALCULATE ( MAX ( 'PaymentTable'[Payment Date] ) )
RETURN
DATEDIFF ( PurchaseTable[purchase date], __pmtTable, DAY )

     

    Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    In the first table, you may write this calculated column formula

    Most recent date = calculate(max('table2'[Payment Date]),filter('table2','table2'[Order Number]=earlier('table2'[Order Number])))

    Hope this helps.

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    Hi dw700d,

     

    Try measure as:

    Measure =

var _date1=

CALCULATE(

    MAX(Table1[purchase date]),

    FILTER(

        Table1,

        Table1[Order number]=MAX(Table2[Order number])

    )

)

var _date2=

CALCULATE(

    MAX(Table2[Payment Date                 ]),

    FILTER(

        ALL(Table2),

        Table2[Order number]=MAX(Table2[Order number])))

return

DATEDIFF(_date1,_date2,DAY)

    Here is the output:

     

    Here is the demo, please try it: Calculating date difference

    Best Regards,

    Link