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 Number Order Date 1111 1/1/2020 2222 1/5/2020 3333 1/10/2020 4444 1/20/2020   Invoice table Order Number I...
  • 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