Forum Discussion
stefans2403
8 years agoFrequent Visitor
Dynamic currency conversion running total
Hi all, I'm trying to calculate running total of a column while at the same time I'm doing currency conversion. Here is my data: So, for example here I have a date slicer placed on 01/31/...
stefans2403
8 years agoFrequent Visitor
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?