Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |