Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have the table below where in i have original currency and transaction currency along with effective date and exchnage rate ,
i have this requirement where in which i want to create a table with with highest date when i have two or more exchange rates within same month and i want to have a column in which i would like to have the previous month exchnage rate if the current exchnage rate is not available.
@mgwena @cham @amitchandak @Greg_Deckler @Mariusz
Input table :
Original curr | Transaction Curr | Effective Date | Exchangerate |
USD | INR | 02-01-2020 | 71 |
USD | INR | 18-01-2020 | 72.05 |
USD | EUR | 07-01-2020 | 14 |
USD | INR | 04-02-2020 | 71.5 |
USD | CAD | 07-02-2020 | 1.01 |
USD | INR | 03-03-2020 | 72 |
Output Table :
Original curr | Transaction Curr | Effective Date | Exchangerate | Previous exchnage rate |
USD | INR | 18-01-2020 | 72.05 | |
USD | EUR | 07-01-2020 | 14 | 72.0 |
USD | CAD | 07-02-2020 | 1.01 | |
USD | INR | 03-03-2020 | 72 | 14 |
Solved! Go to Solution.
Your result does seem in line with the description.
Please find solution at https://www.dropbox.com/s/ow37w9hj8aro25z/LastMonthExchnageRate.pbix?dl=0
Use Max month as filter
Hi,
Create a MonthNo column first:
MonthNo = 'Table'[Effective Date].[MonthNo]
Add an index column in Query Editor.
Then try this measure:
check = IF(MAX('Table'[Effective Date])=MAXX(FILTER(ALL('Table'),'Table'[MonthNo] in FILTERS('Table'[MonthNo])),'Table'[Effective Date]),1,0)
Apply it to original table visual by setting check=1, it shows:
Then try this measure:
Previous exchnage rate = CALCULATE(MAX('Table'[Exchangerate]),FILTER(ALLSELECTED('Table'),'Table'[MonthNo]=MAX('Table'[MonthNo])-1))
The result shows:
Tips: Based on your logic, i think the expected output is like the above not the screenshot you posted.
Please check it.
If i have understood your requirement, please for free to let me know.
Expect your reply!
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
Your result does seem in line with the description.
Please find solution at https://www.dropbox.com/s/ow37w9hj8aro25z/LastMonthExchnageRate.pbix?dl=0
Use Max month as filter
Hello @Truelearner ,
Check-out this great article from SQLBI: https://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/
If you liked my solution please give it a thumbs up 👍.
If I did answer your question please mark my post as a solution ✅.
Thank you !
Cristian
User | Count |
---|---|
86 | |
83 | |
67 | |
61 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
84 | |
70 |