Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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
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?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |