cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Getting the right exchange rate

@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

View solution in original post

10 REPLIES 10
Highlighted
Super User IV
Super User IV

Re: Getting the right exchange rate

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper III
Helper III

Re: Getting the right exchange rate

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? 

Highlighted
Microsoft
Microsoft

Re: Getting the right exchange rate

@Voose

 

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

Getting the right exchange rate_1.jpg

 

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.

Getting the right exchange rate_2.jpg

 

Best Regards,
Herbert

Helper III
Helper III

Re: Getting the right exchange rate

@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

 

 

 

Highlighted
Helper III
Helper III

Re: Getting the right exchange rate

@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

 

 

Highlighted
Microsoft
Microsoft

Re: Getting the right exchange rate

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

 

Getting the right exchange rate_1.jpg

 

Best Regards,
Herbert

Highlighted
Helper III
Helper III

Re: Getting the right exchange rate

Hello again Herbert! @v-haibl-msft,

 

You are correct and there is a simple way to make these unique which I had left out! (apologies) The other filter criteria that will finish this would be to only return currencies within the exchange rate table that have OwnerCompay = "Y" 

 

This is because we have different exchange rates for each of the subsidaries however I already have a column that would filter this out, I had a play myself trying to fit this extra piece of criteria into the code but to no luck, any advice on where it would fit? 

 

Thanks for the =< tip, feel silly for asking now, it was obvious!

 

Thanks

 

Voose

Highlighted
Helper III
Helper III

Re: Getting the right exchange rate

Hi @v-haibl-msft,

 

Hopefully the additional criteria of Ownercompany ="Y" should provide us with a solution that returns a single value insted of multiple!

 

If there is someone that could help add in this extrapiece of criteria here that would be fantastic, I tried adding another Filter function but to no avail. 

 

If we are still in a pickle after this final iteration I'll be posting a sample of the data 🙂

 

Thanks

 

Voose

Highlighted
Microsoft
Microsoft

Re: Getting the right exchange rate

@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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors