Forum Discussion

Voose's avatar
Voose
Helper III
9 years ago
Solved

Getting the right exchange rate

Hi Guys,   I have a table full of timecards. I have a table full of exchange rates. My exchange rate table has a start date (no end date). Each timecard has a date on which it was submitted. Each t...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    Voose

     

     

    You can try with these two formulas. If it still can't work, please give us a sample with all the columns and some data.

     

    RatesWeWant = 
LOOKUPVALUE (
    'Exchange Rate'[c2g__Rate__c],
    'Exchange Rate'[Currency name], 'Timecard Split'[CurrencyIsoCode],
    'Exchange Rate'[OwnerCompany], "Y",
    'Exchange Rate'[c2g__StartDate__c], LASTDATE (
        FILTER (
            ALL ( 'Exchange Rate'[c2g__StartDate__c] ),
            'Exchange Rate'[c2g__StartDate__c] < 'Timecard Split'[pse__End_Date__c]
        )
    )
)

     

    RatesWeWant = 
LOOKUPVALUE (
    'Exchange Rate'[c2g__Rate__c],
    'Exchange Rate'[Currency name], 'Timecard Split'[CurrencyIsoCode],
    'Exchange Rate'[OwnerCompany], 'Timecard Split'[OwnerCompany],
    'Exchange Rate'[c2g__StartDate__c], LASTDATE (
        FILTER (
            ALL ( 'Exchange Rate'[c2g__StartDate__c] ),
            'Exchange Rate'[c2g__StartDate__c] < 'Timecard Split'[pse__End_Date__c]
        )
    )
)

    Best Regards,
    Herbert

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
9 years ago

Voose

 

I create two test tables like this. Maybe they fit you scenario.

 

We can create a calculated column using LOOKUPVALUE function, but we use two more parameters.

RatesWeWant =
LOOKUPVALUE (
    'Exchange Rate'[Rates],
    'Exchange Rate'[CurrencyName], 'TimeCard Split'[Currency],
    'Exchange Rate'[StartDate], LASTDATE (
        FILTER (
            ALL ( 'Exchange Rate'[StartDate] ),
            'Exchange Rate'[StartDate] < 'TimeCard Split'[SubmitTime]
        )
    )
)

You can change the “<” to other operator as you wish.

 

Best Regards,
Herbert

  • Voose's avatar
    Voose
    Helper III
    9 years ago

    v-haibl-msft

     

    Thank you for your reply, I've been playing around with the calculated column solution idea you provided above, here is what im using:

     

    Exchange rate = 
lookupvalue(
            'Exchange Rate'[c2g__Rate__c],
            'Exchange Rate'[Currency name],'Timecard Split'[CurrencyIsoCode],
            'Exchange Rate'[c2g__StartDate__c],LASTDATE(
                    FILTER(
                          all('Exchange Rate'[c2g__StartDate__c]),
                                  'Exchange Rate'[c2g__StartDate__c]<'Timecard Split'[pse__Start_Date__c]
                )
        )
)

    This returns the error - A Table of multiple values was supplied where a single value was expected -> any ideas on fixing this?

     

    Edit - Other than that I think you've cracked it, if the calculated column will display all values for every time card then this would be awesome! I can see in your screen shot that some of the values are blank, shouldn't the 'rateswewant' column for the first row return the value 0.94?

     

    Thanks

     

    Voose

     

     

     

    • Voose's avatar
      Voose
      Helper III
      9 years ago

      v-haibl-msft

       

      This Morning I tried copying and pasting your code directly instead of writing it myself and I still get the same error about 'a table of multiple value was supplied where a single value was expected', using this code:

       

      RatesWeWant = 
LOOKUPVALUE (
    'Exchange Rate'[c2g__Rate__c],
    'Exchange Rate'[Currency name], 'Timecard Split'[CurrencyIsoCode],
    'Exchange Rate'[c2g__StartDate__c], LASTDATE (
        FILTER (
            ALL ( 'Exchange Rate'[c2g__StartDate__c] ),
            'Exchange Rate'[c2g__StartDate__c] < 'Timecard Split'[pse__End_Date__c]
        )
    )
)

       

      Still trying to figure out a fix for this, any help appreciated guys

       

      Thanks

       

      Voose

       

       

      • v-haibl-msft's avatar
        v-haibl-msft
        Microsoft Employee
        9 years ago

        Voose

         

        If you change “<” into “<=”, the first row would return 0.94.

         

        If you have duplicated rows like this in your “Exchange Rate” table, you would encounter this error. If you have other columns which can make the lookup unique, you can add them to the Lookupvalue function like this: Exchange Rate = lookupvalue({parameters we already have}, ‘exchange rate’[ColumnName], ‘Timecard Split’[ColumnName]).

         

        If error still persists, could you please provide some sample data in your table to us?

         

         

        Best Regards,
        Herbert