Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculating Amount Column

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!

1 ACCEPTED 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:

v-kelly-msft_0-1622019150318.png

For the related .pbix file,pls see attached.

 

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

 

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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])

Anonymous
Not applicable

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:

v-kelly-msft_0-1622019150318.png

For the related .pbix file,pls see attached.

 

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

 

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.