Forum Discussion
Difference between two dates
- Anonymous4 years ago
Anonymous 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 - Anonymous4 years ago
Anonymous 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
Anonymous 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?
- Anonymous4 years agoNot applicable
Anonymous 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- Anonymous4 years agoNot applicable
Perfect!
Thank you!
- ryan_mayu4 years agoSuper User
Anonymous
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
- Anonymous4 years agoNot applicable
Thank you!