Forum Discussion

Ataboyata's avatar
Ataboyata
Regular Visitor
9 years ago
Solved

Difference in Dates Measure with Info from Different Tables

Hi, 

I am trying to create a measure that shows the difference between two dates. Each date is stored in a different table and they are referenced to a single ID.

 

For example,

 

Table: Shipping Dates

POid         ShippingDate

A1            12/dec/2016

A2            13/dec/2016

A3            14/dec/2016

 

Table: Payment Dates

POid         PaymentDate

A1            17/dec/2016

A2            18/dec/2016

A3            19/dec/2016

 

What I am looking for is a Measure that shows: Days Payment after Shipment = Payment Date - Shipping Date. 

 

I have done this through a calculated column (Within a third table called 'POHeader') but I would like to take advantage of the capabilities that measures have. 

Days Payment After Shipment =
IF(RELATED('Shipping Dates'[ShippingDate])=BLANK(),BLANK(),
IF(RELATED('Payment Dates'[PaymentDate]=BLANK(),BLANK(),
IF(RELATED('Payment Dates'[PaymentDate]-RELATED('Shipping Dates'[ShippingDate])))

Result:

Table: POHeader

POid        Days Payment after Shipment

A1            5

A2            5

A3            5

 

If anyone knows how to do this within a Measure, please let me know!

 

Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

     

     

     

     

    PLs find the image it will solve your prob 

3 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Ataboyata

     

    Dif-Days =
IF (
    HASONEVALUE ( ShippingDates[POid] ),
    DATEDIFF (
        VALUES ( ShippingDates[Shipping Date] ),
        VALUES ( PaymentDates[PaymentDate] ),
        DAY
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

     

     

     

     

    PLs find the image it will solve your prob 

    • C097986's avatar
      C097986
      Icon for Helper I rankHelper I
      8 years ago

      I use this formula but I keep getting a result of 365 days for all columns. the difference doesn't show up.

       

      Time_diff_RAtoSub = DATEDIFF(MAX('AEF_RESOURCE_ASSIGNMENT_FORM'[Resources Completed].[Date]),MAX('PRIMARY_AEF_REQUEST'[IRM_START_DATE].[Date]),DAY)

       

      Result i get is 365 for each row.

       

      I dont understand where i'm going wrong? Anyone, please help.

      Thank you