Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Difference between two dates

Hi guys,

I have 2 tables as follows,

 

Order table

Order NumberOrder Date
11111/1/2020
22221/5/2020
33331/10/2020
44441/20/2020

 

Invoice table

Order NumberInvoice_dateShipment_num
11111/30/20201
11112/5/20202
22221/20/20201
22221/25/20202

 

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,

Order Completion Time in days = DATEDIFF(Min('Order Table'[Order Date]), Max('Invoice Table'[Invoice Date]), DAY)
and then filtered shipment_num = 1 in the visual table.
 
I am not able to aggregate to find the average no. of days. I am not able to summarize it after the calculation.
 
Any help would be appreciated.
 
Thanks!
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 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

     

  • Anonymous's avatar
    Anonymous
    4 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

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 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