Forum Discussion

aaron1225's avatar
aaron1225
Helper I
8 years ago
Solved

Indexing w/ Lookup?

Running into some trouble hoping someone can help.

 

I have two tables.  One table is a currency conversion table.  It has a Validity Start Date & a Validity End Date, then an ISO Currency Code and a Conversion Rete.

 

 

 

Second table has sales with Company Name, Currency Code, Amount, Close Date:  

 

 

I need to create a measure (or column) using DAX that will look at my Sales Table Currency Code & Close Date, index my Conversion Table to find the correct date range that corresponds to the close date and return the conversion rate of the corresonding ISO Code.  The end product should be the Sales Amount Converted.

 

Any help would be greatly appreciated.  

 

 

Need Help

9 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    8 years ago

    aaron1225

     

     

    Hi, please try with this calculated column:

     

    Amount Converted =
CALCULATE (
    VALUES ( Table1[ConversionRate] ),
    FILTER (
        Table1,
        Table1[CurrencyCode] = Table2[CurrencyCode]
            && Table1[Validity Start Date] <= Table2[Close Date]
            && Table1[Validity End Date] >= Table2[Close Date]
    )
)
    * Table2[Amount]

    • aaron1225's avatar
      aaron1225
      Helper I
      8 years ago

      Thank you, this worked on a small subset of data (one quarter of a fiscal year), however once I loaded more historic data I got an error:

       

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        8 years ago

        aaron1225

         

        Do you have more than one Conversion Rate to a CurrencyCode in equals Start and End Date?

         

        Regards

         

        Victor

        Lima -Peru