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

 

 

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
Icon for Helper III rankHelper 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