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 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
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