Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone,
I'm really stuck and I would highly appreciate some help. I'm pretty new with Power BI, not even 2 weeks of experience.
I'll try make this as simple as possible, I really appreciate your time.
1. I have a GL Entry table with the columns Amount and Posting Date.
Posting Date | Exchange_Rate
2/13/2021 20.72
2/13/2021 78.35
2/17/2021 41.27
2/18/2021 43.28
etc...
2. I also got a Currency Exchange Rate table with 2 columns I'm interested in ( this only contains the exchange rate for EUR ).
Date | Exchange_Rate
2/15/2021 4.8734
2/16/2021 4.8751
2/17/2021 4.8747
2/18/2021 4.8741
etc..
Basically I want to create a new column in GL_Entry called... Amount EUR that contains the Amount value in EUR , calculated with the average of the Exchange_Rate of every month.
Let's say the average exchange rate for 03/2021 was 4.8790, then all the amounts ( Amount EUR ) in the GL Entry table for that month will be calculated with that average exchange rate.
I'm kind of stuck because I don't know how to associate the certain dates from the GL Entry with those from the Currency Exchange Rates.
Any help is grealy appreciated, thanks for your time!
Have a great day!
Solved! Go to Solution.
Hi @Anonymous ,
First create a month column to get the month number in 2 tables;
Then create 2 columns respectively in the 2 tables:
average exchange rate = AVERAGEX(FILTER(ALL('Currency Exchange Rate'),'Currency Exchange Rate'[Month No]=EARLIER('Currency Exchange Rate'[Month No])),[ Exchange_Rate])
average exchange rate = LOOKUPVALUE('Currency Exchange Rate'[average exchange rate],'Currency Exchange Rate'[Month No],'GL Entry'[Month No],0)
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
@Anonymous , Create a new column in
'GL Entry' table
Amount in EURO =
[Amount] * AverageX(filter('Currency Exchange Rate', eomonth('Currency Exchange Rate'[Posting Date],0) = eomonth('GL Entry'[Date],0)),'Currency Exchange Rate'[Exchange_Rate])
Hello,
Thanks for the response but I think the averageX only takes the exchange rate from the last day of every month in the calculation, correct me if I'm wrong. Anyway, your response helps me understand DAX better.
I need to make the average exchange rate of all days in one month then calculate the Amount EUR column for the same month. Same for every month.
Sorry if I got something wrong 🙂
Hi @Anonymous ,
First create a month column to get the month number in 2 tables;
Then create 2 columns respectively in the 2 tables:
average exchange rate = AVERAGEX(FILTER(ALL('Currency Exchange Rate'),'Currency Exchange Rate'[Month No]=EARLIER('Currency Exchange Rate'[Month No])),[ Exchange_Rate])
average exchange rate = LOOKUPVALUE('Currency Exchange Rate'[average exchange rate],'Currency Exchange Rate'[Month No],'GL Entry'[Month No],0)
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hello kelly,
Sorry for the late response. I also needed to add the year in the "Month No" column but it was just a small detail.. Your implementation worked perfectly, thanks for the help!
Best regards
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |