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 appreci...
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!

     

     

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!

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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