Forum Discussion
Using exchange rates
- 1 year ago
You need to make a couple of changes in your model. First, change the type of the [MRR] column to a decimal number. Also link 'Date'[Date] to 'Exchange Rates'[Time Period]. Then you can create a measure like
MRR Revenue Converted = VAR BaseTable = SUMMARIZE( 'Exchange Rates', 'Dates2'[Month Year], 'Exchange Rates'[Source], 'Exchange Rates'[Rate] ) VAR ConvertedValue = SUMX( BaseTable, VAR MaxDate = CALCULATE( MAX( 'Dates2'[Date] ) ) VAR Source = 'Exchange Rates'[Source] VAR Result = CALCULATE( SUM( TblCombinedx[MRR] ), TblCombinedx[Calculated End Date] > MaxDate, TblCombinedx[(estimated) (billing) start date] <= MaxDate, TblCombinedx[PON/Other] IN { "PON", "Excluded" }, TblCombinedx[Currency] = Source ) * 'Exchange Rates'[Rate] RETURN Result ) VAR UnconvertedValue = SUMX( VALUES( Dates2[Month Year] ), VAR MaxDate = CALCULATE( MAX( 'Dates2'[Date] ) ) VAR Result = CALCULATE( SUM( TblCombinedx[MRR] ), TblCombinedx[Calculated End Date] > MaxDate, TblCombinedx[(estimated) (billing) start date] <= MaxDate, TblCombinedx[PON/Other] IN { "PON", "Excluded" }, ISBLANK( TblCombinedx[Currency] ) ) RETURN Result ) VAR Result = ConvertedValue + UnconvertedValue RETURN Result - 1 year ago
The line
TblCombinedx[Currency] = Sourcemakes sure that it is the correct currency.
The line
TblCombinedx[Currency] = Source
makes sure that it is the correct currency.
Thanks very much. I have a follow up question if I may.
I want to try to minimise the number of measures I have that do a similiar thing.
There are three types of visuals I am trying to do with this data:
1) Revenue trend over time - This nails that
2) focus on a particular month - I can use the same measure with the month selected from dates2 as a visual filter.
3) Difference between two months - This has me stuck.
Effectively I need to create a measure that filters the measure 'MRR Revenue Converted' by a month (say december) and then subtract from that 'MRR Revenue Converted' that is filtered by a different month (say november).
Is that possible please?
Or do I need to add measure filtered on december and on November first?
- Lodan1 year agoHelper II
Ignore this please. I was waaay over thinking it and not looking at the obvious (and simple) way.