Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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:

 

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
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.