Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

How to get value from another table based on condition and specific period

Hello,

 

I'm in stuck with my problem. Let's say I have 2 tables: Quotes and Shipments. My goal is to transfer QuoteID from Quotes table into Shipments table if such conditions are met:

 

Condintion = Condition

Departure is between StartDate and ExpiryDate

 

please see my file via this link

Need Help
Show and Tell
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    You could try this:

    Quote ID =
CALCULATE (
    FIRSTNONBLANK ( Quotes[Quote ID], 1 ),
    FILTER (
        Quotes,
        Quotes[Condition] = EARLIER ( Shipments[Condition] )
            && EARLIER ( Shipments[Departure] ) >= Quotes[StartDate]
            && EARLIER ( Shipments[Departure] ) <= Quotes[ExpiryDate]
    )
)

    Best regards,

    Yuliana Gu

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

     

    You could try this:

    Quote ID =
CALCULATE (
    FIRSTNONBLANK ( Quotes[Quote ID], 1 ),
    FILTER (
        Quotes,
        Quotes[Condition] = EARLIER ( Shipments[Condition] )
            && EARLIER ( Shipments[Departure] ) >= Quotes[StartDate]
            && EARLIER ( Shipments[Departure] ) <= Quotes[ExpiryDate]
    )
)

    Best regards,

    Yuliana Gu