Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys,
I have 2 tables as follows,
Order table
Order Number | Order Date |
1111 | 1/1/2020 |
2222 | 1/5/2020 |
3333 | 1/10/2020 |
4444 | 1/20/2020 |
Invoice table
Order Number | Invoice_date | Shipment_num |
1111 | 1/30/2020 | 1 |
1111 | 2/5/2020 | 2 |
2222 | 1/20/2020 | 1 |
2222 | 1/25/2020 | 2 |
Both these tables have a 1-many relationship as you can see above.
How do I create a measure to find the difference between the dates (Invoice Date - Order Date) to find the number of days? I want it for shipment_num = 1 only.
I tried,
Solved! Go to Solution.
@gunasai are you attempting to do this in a measure or a calculated column?
One way to do it would be with a calculated column, added to the invoice table. Formula might look something like:
Order Completion Time in Days =
VAR _Invoice_Date = 'Invoice Table'[Invoice_date]
VAR _Order_Date = LOOKUPVALUE('Order Table'[Order Date],'Order Table'[Order Number],'Invoice Table'[Order Number])
VAR _Result = DATEDIFF(_Order_Date, _Invoice_Date, DAY)
Return
_Result
@gunasai a similar approach could be used with a measure in a table visual, but in order for it to work properly you would need to ensure that only a single "Invoice_Date" and "Order Number" are in "context" (in other words, you need to include Order Number and Invoice Date in your visual).
I would also use SELECTEDVALUE() to ensure the measure doesn't return anything if there are more than one Order Number and Invoice Date in context.
Order Completion Time in Days (measure) =
VAR _Invoice_Date =
SELECTEDVALUE ( 'Invoice Table'[Invoice_date] )
VAR _Order_Date =
LOOKUPVALUE (
'Order Table'[Order Date],
'Order Table'[Order Number], SELECTEDVALUE ( 'Invoice Table'[Order Number] )
)
VAR _Result =
DATEDIFF ( _Order_Date, _Invoice_Date, DAY )
RETURN
_Result
@gunasai are you attempting to do this in a measure or a calculated column?
One way to do it would be with a calculated column, added to the invoice table. Formula might look something like:
Order Completion Time in Days =
VAR _Invoice_Date = 'Invoice Table'[Invoice_date]
VAR _Order_Date = LOOKUPVALUE('Order Table'[Order Date],'Order Table'[Order Number],'Invoice Table'[Order Number])
VAR _Result = DATEDIFF(_Order_Date, _Invoice_Date, DAY)
Return
_Result
THIS WORKED!!!
Thank you so much!
I never did a calculated column before when there are two tables. Thank you so much for this again!
Just out of curiosity to learn, how would I do the same if I wanted to do a measure?
you can also try this
Measure = DATEDIFF(max('Order'[Order Date]),MAXX(FILTER(Invoice,Invoice[Shipment_num]=1),'Invoice'[Invoice_date]),DAY)
pls see the attachment below
Proud to be a Super User!
Thank you!
@gunasai a similar approach could be used with a measure in a table visual, but in order for it to work properly you would need to ensure that only a single "Invoice_Date" and "Order Number" are in "context" (in other words, you need to include Order Number and Invoice Date in your visual).
I would also use SELECTEDVALUE() to ensure the measure doesn't return anything if there are more than one Order Number and Invoice Date in context.
Order Completion Time in Days (measure) =
VAR _Invoice_Date =
SELECTEDVALUE ( 'Invoice Table'[Invoice_date] )
VAR _Order_Date =
LOOKUPVALUE (
'Order Table'[Order Date],
'Order Table'[Order Number], SELECTEDVALUE ( 'Invoice Table'[Order Number] )
)
VAR _Result =
DATEDIFF ( _Order_Date, _Invoice_Date, DAY )
RETURN
_Result
Perfect!
Thank you!
User | Count |
---|---|
93 | |
83 | |
77 | |
71 | |
65 |
User | Count |
---|---|
114 | |
101 | |
96 | |
65 | |
60 |