cancel
Showing results for
Did you mean:
stefans2403 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:  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): 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

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

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

stefans2403 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: Best Regards,

Stefan

stefans2403 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?

Announcements #### 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. Learn the answers to some of the questions asked during the Amanda Triple A event. #### 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.  Top Kudoed Authors
Users Online
Currently online: 163 members 2,069 guests
Recent signins:
• Uzi2019 • doni • scottjd • • SivaranjaniA • johnmelbourne • Sameeksha • TYTY • okaplan • farezmd • dkassian • XZENG13 • M-F • Breacher 