Forum Discussion

Will2020's avatar
Will2020
Frequent Visitor
6 years ago
Solved

Latest currency conversion from other table

I have a table as above called vwConversion which shows conversion rates between USD and other global currencies. There is one conversion rate per year produced on 01/10.  There are multiple o...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Will2020 ,

    Please try to create a calcualted table as below, then create the relationship between new created table and vwRevenues based on currency field.

    Table = 
SUMMARIZE (
    VALUES ( vwConversion[FROM_CURRENCY] ),
    'vwConversion'[FROM_CURRENCY],
    "LatestConversionRate", CALCULATE (
        MAX ( 'vwConversion'[CONVERSION_RATE] ),
        FILTER (
            ALLSELECTED ( 'vwConversion'[CONVERSION_DATE] ),
            'vwConversion'[CONVERSION_DATE] = MAX ( 'vwConversion'[CONVERSION_DATE] )
        )
    )
)

    Best Regards

    Rena

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Will2020 ,

Did you create any relationship between table vwConversion and vwRevenues just like below?

Best Regards

Rena

Will2020's avatar
Will2020
Frequent Visitor
6 years ago

hi

thanks again for your help

I can't create a relationship because there is technically a many to many relationship, as for each currency there are 5 or so lines?

I can't create a relationship between a column on the revenues table, and a measure on the conversion ( can't link between a measure?)

Maybe I could create a new table which just pulls only the latest data from the conversion table...

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Will2020 ,

    Could you please provide some sample data in table vwRevenues? Later we will check if there are other feasible ways to achieve it.

    Best Regards

    Rena

    • Will2020's avatar
      Will2020
      Frequent Visitor
      6 years ago

      Here 

       

      the issue is that there are 1000's of REVENUE_ID's and each one has a different CURRENCY.

      So you could tie that CURRENCY column to the conversion rates table, but the conversion rates also has multiple FROM_CURRENCY (as it is updated yearly). So it creates a many>many relationship.

      I need to tie the CURRENCY to the measure which you helped me create, but that seems to be impossible with directquery. 

      I have tied a custom column through a relationship before, though. So maybe we could generate a column with the measure value in it?

      thanks again for your help.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Will2020 ,

        Please try to create a calcualted table as below, then create the relationship between new created table and vwRevenues based on currency field.

        Table = 
SUMMARIZE (
    VALUES ( vwConversion[FROM_CURRENCY] ),
    'vwConversion'[FROM_CURRENCY],
    "LatestConversionRate", CALCULATE (
        MAX ( 'vwConversion'[CONVERSION_RATE] ),
        FILTER (
            ALLSELECTED ( 'vwConversion'[CONVERSION_DATE] ),
            'vwConversion'[CONVERSION_DATE] = MAX ( 'vwConversion'[CONVERSION_DATE] )
        )
    )
)

        Best Regards

        Rena