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!

     

     

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