Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi, @Anonymous
Please check the below picture and the sample pbix file's link down below.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
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?
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hello @Jihwan_K
My Requirement is solved now. Thank you for your help.
Thank you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |