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

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

  • Voose's avatar
    Voose
    Helper III
    9 years ago

    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

  • Voose's avatar
    Voose
    Helper III
    9 years ago

    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

    • v-haibl-msft's avatar
      v-haibl-msft
      Microsoft Employee
      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

      • Voose's avatar
        Voose
        Helper III
        9 years ago

        v-haibl-msft,

         

        Not only has this worked but you've now also completely made me understand how this piece of DAX works! I've also deployed it in two other areas where i was having issues and its worked a charm.

         

        Can't be thankful enough Herbet!

         

        Voose