Forum Discussion

GraceTCL's avatar
GraceTCL
Icon for Helper II rankHelper II
5 years ago
Solved

Convert currency

Hi I need to convert the local currency to reporting currency. In the exchange rate dimension table, the date reference is based on start or end date of the month while my fact table is based on cal...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, GraceTCL 

    Thank you for sharing the data.

     

    - sorry to say that I could not use the date-id column effectively because my computer generally uses mm/dd/yyyy format. And, it is quite easy for me to create an effective measure when using the actual date column, instead of using date_id column in number format.

    - I disconnected one relationship. please check the below picture and the sample pbix file's link down below.

     

     

    Renue Exchange rate apply =
    SUMX (
    VALUES ( 'Calendar'[Date] ),
    CALCULATE (
    SUMX (
    'Food Metrics',
    'Food Metrics'[Revenue_LC]
    * CALCULATE (
    SELECTEDVALUE ( 'Exchange rate'[Exchange rate] ),
    FILTER (
    'Exchange rate',
    MIN ( 'Calendar'[Date] ) >= 'Exchange rate'[Start Date]
    && MAX ( 'Calendar'[Date] ) <= 'Exchange rate'[End Date]
    )
    )
    )
    )
    )
     
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

Hi, GraceTCL 

I am not sure if I understood your question correctly, but I just assumed and created a sample pbix file like below.

Please check the below picture and the link down below, whether it suits your case.

all measures are in the sample pbix file.

 

https://www.dropbox.com/s/5ewsg6icb0hazdb/grace.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

GraceTCL's avatar
GraceTCL
Icon for Helper II rankHelper II
5 years ago

Jihwan_Kim Thanks for your fast response. Unfortunately it doesnt work.

 

Here's more details on how the table are linked by following the yellow highlight.

 

The outcome that I had by following your advice. Still null.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    Hi, GraceTCL 

    Thank you for your feedback.

    In my opinion, your model and my model are totally different. I think my measure cannot be effectively used in your model.

    And I am not sure about your date_id column and relationships between tables.

    Please share your sample pbix file's link here, then I can try to come up with a more accurate measure, and I also want to suggest changing a relationship.

     

    Thanks.

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        5 years ago

        Hi, GraceTCL 

        Thank you for sharing the data.

         

        - sorry to say that I could not use the date-id column effectively because my computer generally uses mm/dd/yyyy format. And, it is quite easy for me to create an effective measure when using the actual date column, instead of using date_id column in number format.

        - I disconnected one relationship. please check the below picture and the sample pbix file's link down below.

         

         

        Renue Exchange rate apply =
        SUMX (
        VALUES ( 'Calendar'[Date] ),
        CALCULATE (
        SUMX (
        'Food Metrics',
        'Food Metrics'[Revenue_LC]
        * CALCULATE (
        SELECTEDVALUE ( 'Exchange rate'[Exchange rate] ),
        FILTER (
        'Exchange rate',
        MIN ( 'Calendar'[Date] ) >= 'Exchange rate'[Start Date]
        && MAX ( 'Calendar'[Date] ) <= 'Exchange rate'[End Date]
        )
        )
        )
        )
        )
         
         
         

        Hi, My name is Jihwan Kim.


        If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


        Linkedin: linkedin.com/in/jihwankim1975/

        Twitter: twitter.com/Jihwan_JHKIM