FX conversion based on multiple exchange pairs and dates

[Anonymous]

Hi,

I have issues obtaining one specific currency pair from a FX table. I have the following table: 

There are multiple dates and various currency pairs. The base currency is EUR, so all BaseFxRates are given in EUR. I need to create a new column with GBP as the BaseFxRate. So basically divide the BaseFxRate by the corresponding GBP for the respective date. I tried to create a new column with all GBP rates, filling up the same rate for each of the dates, afterwards I would create a new column = BaseFxRate / GBP in order to get GBP as base currency. But I can't make the rates date-dependent, thus I am getting the same rate for each row.

Creating it directly with a custom column must be possible somehow, but I can't figure out how to.

Any help would be most appreciated.

Thanks 🙂

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[amitchandak]

@Anonymous , remove .[day]

if the date has a timestamp then use .Date . Try to avoid that if there is no timestamp in date

[Anonymous]

@amitchandak Thank you, I had that before though, it doesn't change anything, still looks the same.

And fill down or something the like is not working either, because the currency pairs GBP:EUR is always somewhere in the middle of one day. So I somehow need to catch that and fill the rows for each day with this one value.

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Try like this:

GBP = 
CALCULATE(
    LASTNONBLANK('FxRates'[GBP value],1),
    FILTER(
        'FxRates',
        'FxRates'[Date] = EARLIER(FxRates[Date])
    )
)

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.