Forum Discussion

Voose's avatar
Voose
Icon for Helper III rankHelper 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 timecard also has the currency in which it was submitted. How do I get the correct exchange rate to land on each time card.

 

Initially when thinking about this I was thinking about finding the highest start date that is less than the date of the Timecard and then returning the value that is the exchange rate where the exchange currencies are the same...

 

Let me know your thoughts guys

 

Voose

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

10 Replies

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

    You might look at generating your exchange rate table such that it includes all dates with the appropriate exchange rate. Then you could do a simple relationship. Failing that, you should be able to do a "lookup" to the exchange rate table to find the MAX date that is less than the date in your timecard.

    • Voose's avatar
      Voose
      Icon for Helper III rankHelper III
      9 years ago

      Hey Greg_Deckler,

       

      Can't create a relationship I'm afraid, but I did try the lookup... Keeping it simple to begin with and exclusing the dates part (New to using lookups)

       

      I have used this -> Exchange rate = LOOKUPVALUE('Exchange Rate'[c2g__Rate__c],'Exchange Rate'[Currency name],'Timecard Split'[CurrencyIsoCode])

       

      I was hoping this would just give me an exchange rate as a test, but I assume that this isn't working because it is returning multiple values? 

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft 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
      Icon for Helper III rankHelper 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
        Icon for Helper III rankHelper 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