Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Currency Conversion

Hello,

 

I have two tables Currency Exchange rate table and Calender table.

Note: There is no link between both tables.

 

Currency exchange table with Currency slicer (USD) 

 

Calender Table with may slicer

Conversion rate column which is highlited is the required column. 

 

I want to write forumale to Conversion rate column in Calender table which is highlited.

 

Requirement is as follows.

1. Conversion Rate existed for may in 05/04/2021 in Currency exchange table as 2.004 so before 05/04/2021 in Currency exchange table Conversion Rate existed for 04/29/2021as 6.999 i.e 05/01/2021 to 05/03/2021 Conversion rate in Calender table is 6.999

 

2. Conversion Rate existed for 05/04/2021 in Currency exchange table as 2.004

 

3. After 05/04/2021 Conversion Rate existed for 05/19/2021 in Currency exchange table as 5.556 so before 05/19/2021 in Currency exchange table Conversion Rate existed for 05/04/2021as 2.004 i.e 05/05/2021 to 05/18/2021 Conversion rate in Calender table is 2.004

 

4. Conversion rate existed for 05/19/2021 in Currency exchange table as 5.556

 

5. After 05/19/2021 Conversion Rate existed for 05/26/2021 in Currency exchange table as 9.234 so before 05/26/2021 in Currency exchange table Conversion rate existed for 05/19/2021 as 5.556 i.e 05/20/2021 to 05/25/2021 Conversion rate in Calender table is 5.556

 

6. Conversion Rate existed for 05/26/2021 in Currency exchange table as 9.234

 

7. After 05/26/2021 Conversion Rate existed for 05/29/2021 in Currency exchange table as 6.644 so before 05/29/2021 in Currency exchange table Conversion Rate existed for 05/26/2021 as 9.234 i.e 05/27/2021 to 05/28/2021 Conversion Rate in Calender table is 9.234

 

8. Conversion Rate existed for 05/29/2021 in Currency exchange table as 6.644

 

9. finally before 05/31/2021 and 05/30/2021 have 6.644 because Conversion Rate existed for 05/29/2021 in Currency exchange table as 6.644. 

 

Thank You

 

 

 

 

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Anonymous 

    Sorry that I did not put = into the measure.

    Please check the below.

     

     

     

    https://www.dropbox.com/s/gt34fq2w3nfmy2h/venkat.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

7 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, Anonymous 

    Please check the below picture and the sample pbix file's link down below.

     

     

    Conversion Rate Measure =
    VAR currentdate =
    MAX ( 'Calendar'[Date] )
    VAR currentcode =
    MAX ( 'Currency'[Currency Code] )
    VAR latestdatecurrency =
    CALCULATE (
    MAX ( 'Currency'[Date] ),
    FILTER (
    ALL ( 'Currency' ),
    'Currency'[Currency Code] = currentcode
    && 'Currency'[Date] < currentdate
    )
    )
    RETURN
    IF (
    ISFILTERED ( 'Calendar'[Date] ),
    CALCULATE (
    MAX ( 'Currency'[Conversion Rate] ),
    'Currency'[Date] = latestdatecurrency
    )
    )
     
     
     
     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello Jihwan_Kim

       

      Please see my Highlited Conversion Rate output in calender table.

       

      According my output. 05/04/2021 is 2.004 (which is not in your output) and 05/19/2021 is 5.5556 (which is not in your output) and 05/26/2021 is 9.234 (which is not in your output) and 05/29/2021 is 6.644 (which is not in your output)

       

      NOTE : The logic is if Conversion Rate is existed for calender date it has to print that Conversion Rate other wise it has to print the Previous Conversion Rate

       

      Could you please modify the measure according to my requirement. (if possible plese write functionality of indivual code in dax with # so that we can understand the code in better way)

       

      I am unable to see the Conersion Rate properly (as my Currency Exchange table looks) with respect to date in the Currency table in your attached pbi file

       

      Thank You

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        5 years ago

        Hi, Anonymous 

        Sorry that I did not put = into the measure.

        Please check the below.

         

         

         

        https://www.dropbox.com/s/gt34fq2w3nfmy2h/venkat.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

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

    You need to be able to apply a slicer on the currency table then populate the rates against the dates in the calendar table as per your example. In that case, adding a calculated column to the calendar table will not work. You need to have measure created for it.

    Please confim