Currency Conversion

[Anonymous]

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

[Fowmy]

@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

[Jihwan_Kim]

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

)

)

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

[Anonymous]

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 · Accepted solution]

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

[Anonymous]

Hi Jiwhan,

Thank for the help. the measure is almost working. but when we select april month in the calender table. if there is no data for april month in the Currency exchange table( i.e the Currency Exchang table date starts with may) the measure throwing empty table. but according to my scenerio the measure should filter with may value for the april calender table date.

Thank you

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

Sorry that I quite do not understand your last question.

-  You do not have April's currency rate, but you want to see April's currency rate?

[Anonymous]

Hello @Jihwan_K

My Requirement is solved now. Thank you for your help.

Thank you