Forum Discussion
Date Difference of Latest Two Values for Each Order
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?
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
5 Replies
v-jiascu-msftMicrosoft Employee
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
Thank you Dale!
It works. :smileywink:
- tex628
Community Champion
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! :)
- 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!
