cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stefans2403 Frequent Visitor
Frequent Visitor

Calculating Running Total using currency exchange rate, per year

Hi PBI Community,

 

I am trying to calculate cummulative amount using conversion rate of last date of year or last date in selected period , per year:

 

b.png

 

 

 a.png

 

Thing is, as shown in ss below, Total cummulative amount is calculated only using rate of rate of 31.12.2016, while I am trying to force DAX to use rate of 31.12.2015 for cummulative amount at end of 2015 (=26,111,090) and 31.12.2016 rate for amount in accumulated in 2016 (5,648,555):

d.png

So, I need a help on how to calculate Cummulative Amount EUR using currency exchange rate of last day of year or day of year if some other day is selected?

 

Thank you

 

3 REPLIES 3
Super User
Super User

Re: Calculating Running Total using currency exchange rate, per year

Sample data please.

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

stefans2403 Frequent Visitor
Frequent Visitor

Re: Calculating Running Total using currency exchange rate, per year

Hi Greg,

 

Here is the pbix with sample data: Sample PBIX

 

And here are the numbers of what am I getting and what is expected result with filter set on 02/02/2017:

2018-04-03 18_13_27-Clipboard.png

 

 

 

 

Best Regards,

Stefan

 

stefans2403 Frequent Visitor
Frequent Visitor

Re: Calculating Running Total using currency exchange rate, per year

This is what I managed to calcuate:

 

Amount Cumulative = 
CALCULATE (
    SUM('dwh FactGLbs'[Amount]),
    FILTER ( ALLEXCEPT(DimDate,DimDate[Year] ), 'DimDate'[Date] <= MAX ( 'DimDate'[Date] ))
)

and conversion:

Amount Cummulative EUR = 
VAR CurrentExchangeRate =
    CALCULATE (
        MAX ( 'dwh DimCurrencyExchangeRate'[Exchange Rate] ),
        LASTNONBLANK (
            DimDate[Date],
            MAX ( 'dwh DimCurrencyExchangeRate'[Exchange Rate] )
        )
    )
RETURN
    DIVIDE( [Amount Cummulative], CurrentExchangeRate )

but the thing is that I am not getting desired ExchangeRate.

 

Using this formula, for example, Running Total for 2016 is calculated using SUM('dwh FactGLbs'[Amount]) but is dividied by ExchangeRate of 31/12/2016.

 

I need Amount Cummulative EUR for 2016 to be calculated like: (SUM('dwh FactGLbs'[Amount] for 2015) * ExchangeRate of 31/12/2015 )+(SUM('dwh FactGLbs'[Amount] for 2016) * ExchangeRate of 31/12/2016 ), etc.

 

Does anyone have an idea?

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 163 members 2,069 guests
Please welcome our newest community members: