Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

FX conversion based on multiple exchange pairs and dates

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 🙂

Need Help
  • v-yingjl's avatar
    v-yingjl
    4 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , remove .[day]

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        v-yingjl
        Community Support
        4 years ago

        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.