Forum Discussion

stefans2403's avatar
stefans2403
Frequent Visitor
8 years ago

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/2017. In this case, 2015 amount should divide 'Amount' with rate of a last date in that year or slicer date (for 2015 it should take rate of 12/31/2015, for 2016 rate of 12/31/2016 and for 2017 rate for 01/31/2017). That is what I got in 'Amount EUR per Year' column, but for some reason Total is wrong:

 

 

 

 

 

 

 

 

 

 

 

 

So basically at the end I need to calculate running total of 'Amount EUR per Year' column:

 

 

 

 

 

 

 

How could I manage this?

 

Here is the sample PBIX file.

 

Thanks!

Need Help

1 Reply

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