Calculating date difference

[dw700d]

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 number	purchase date	cost
1	1/1/2020	300
2	1/2/2020	400
3	1/3/2020	100
4	1/4/2020	500
5	1/5/2020	900
6	1/6/2020	250
7	1/7/2020	100
8	1/8/2020	400
9	1/9/2020	100
10	1/10/2020	150
11	1/11/2020	200
12	1/12/2020	300

Order number	Payment Date	cost
1	3/1/2020	100
1	4/5/2020	100
1	5/1/2020	100
2	2/2/2020	400
4	3/2/2020	300
4	3/2/2020	100
4	3/2/2020	100
6	3/22/2020	150
7	4/1/2020	50
7	4/2/2020	50
8	5/2/2020	200
8	5/8/2020	200

[v-xulin-mstf · Accepted solution]

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

[Ashish_Mathur]

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.

[parry2k]

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