Forum Discussion

stefans2403's avatar
stefans2403
Frequent Visitor
8 years ago

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 i...
Need Help
stefans2403's avatar
stefans2403
Frequent Visitor
8 years ago

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's avatar
stefans2403
Frequent Visitor
8 years ago

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?