Date Difference of Latest Two Values for Each Order

[amirsohani]

Hi there,

I have two tables:

1. Orders

Order ID	Price	Order Duration
1	$       2.00	?
2	$       5.00	?
3	$       3.00	?

2. Orders Status

ID	Order ID	Status	Created at
1	1	10	7/10/18 12:00 AM
2	1	20	7/10/18 12:18 AM
3	1	30	7/10/18 12:21 AM

I want to calculate date difference (minute) of latest two dates (ID:2 and ID:3 in order status table) for each order.

what is the DAX syntax for it?

[v-jiascu-msft · Accepted solution]

Hi @amirsohani,

Try the demo out in the attachment. The two tables should have relationship. I have added more data to test.

Column =
VAR lastestTwo =
    CALCULATETABLE (
        TOPN ( 2, 'Orders Status', 'Orders Status'[Created at], DESC )
    )
RETURN
    DATEDIFF (
        MINX ( lastestTwo, [Created at] ),
        MAXX ( lastestTwo, [Created at] ),
        MINUTE
    )

Best Regards,

Dale

[amirsohani]

Thank you Dale!

It works. 

[tex628]

Alright, due to the format of your order status table you will need to do something like this:

This is your order status table. A little simplified.

In the Query editor, use the "Transpose" feature. You should get something like this:

I would then use the "Use first row as headers" feature. See the previous picture.

Finally what you do is create a custom column, still in the query editor. Use the simple code [3] - [2].

Hope this helps! 🙂

[amirsohani]

This is a sample table,
I just put 3 rows in order status table but it may have dozens of records for each order.
So your solution doesn’t help me in this case.

[tex628]

I realised that... In general, referring between two rows in the same column is alot harder than referring to two columns in the same row. 

You might be able to solve it using EARLIER()

If you filter the table by ID you might be able to collect both values that way. 

Eitherway, that format is more trouble than it's worth but good luck!