Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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!

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

     

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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 🙂

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        5 years ago

        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!