Using exchange rates

[Lodan]

Hello,

Today I have a visual that calculated the sum of the total MRR as long as the start date is before and the end date is after.  It is linked to the dates table so it does is on a monthly basis.  I then lists this in a table for each month and a separate one with it rolled up to the year (PBIX is attached).

It works well but I would like tov take it to the next level.  Instead of sum'ing the MRR based on the ($) column (which was a one time conversion at start date) I would like it to account for currency changes month to month.

The table has a currency column and the value in that currency.  I have also added an exchange rates table.

Effectively what I want to change it to is if the currency field has a value to then multiply it by the exchange rate for that currency and for that month and if the currency column doesn't have a value to tahe the ($) amount and then sum them up on as monthly basis.

Not entirely sure I am making sense.  Let me know if not please.  I am trying to work out the best way of achieving this and am struggling.  Any help would be appreciated please.

Test.pbix

Thanks and regards

[johnt75 · Accepted solution]

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

[Lodan]

Thanks very much.  I think that gets me most of my way there.

Annoyingly that looks better in the test file than in my live file.  I will try to cut the love one down enough to share that later.  

For this part.  If I understand correctly, it create a new summary table with 'month year', Currency and rate in it.  There are two currencies and rates for each month.

We then effectively filter the sumx calculation by whether the currency in 'tblcombined' is in 'Exchange Rates' thus effectively filtering down the USD ones which we handle in the second part.

Then we multiple that sum by the exchange rate.  Does it know at this point which exchange rate please?

We don't have a relationship connecting the two currencies so I was wondering how did it know to match EUR in 'tblcombined' to EUR in 'Exchange rates' to thus pick the (for example) January 2024 EUR rate and not the January 24 GBP rate.

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

There is obviously also the possibility that I am off my rocker and it is correct its just that the exchange rates went down since it was first billed.  I will pick one of the months and do an excel equation to check that too.

Regards

[Lodan]

I think i have sorted it, although it might not be in the best / easiest manner.

I duplicated the exhcnage rate table and then made it so I had GBP in one (called GBP) and EUR in the other (called EUR).  Then I did this and the values look better:

MRR Revenue Converted2 = 
VAR BaseTable = SUMMARIZE( 'GBP', 'Dates2'[Month Year], 'GBP'[Source], 'GBP'[Rate] )
VAR ConvertedValueGBP = SUMX(
	BaseTable,
	VAR MaxDate = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Source = 'GBP'[Source]
	VAR Result = CALCULATE(
		SUM( TblCombined[MRR] ),
		TblCombined[Calculated End Date] > MaxDate,
        TblCombined[(estimated) (billing) start date] <= MaxDate,
        TblCombined[PON/Other] IN { "PON", "Excluded" },
		TblCombined[Currency] = Source
	) * 'GBP'[Rate]
	RETURN Result
)
VAR BaseTable2 = SUMMARIZE( 'EUR', 'Dates2'[Month Year], 'EUR'[Source], 'EUR'[Rate] )
VAR ConvertedValueEUR = SUMX(
	BaseTable2,
	VAR MaxDate = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Source = 'EUR'[Source]
	VAR Result = CALCULATE(
		SUM( TblCombined[MRR] ),
		TblCombined[Calculated End Date] > MaxDate,
        TblCombined[(estimated) (billing) start date] <= MaxDate,
        TblCombined[PON/Other] IN { "PON", "Excluded" },
		TblCombined[Currency] = Source
	) * 'EUR'[Rate]
	RETURN Result
)
VAR UnconvertedValue = SUMX( VALUES( Dates2[Month Year] ),
	VAR MaxDate = CALCULATE( MAX( 'Dates2'[Date] ) )
	VAR Result = CALCULATE(
		SUM( TblCombined[MRR ($)] ),
		TblCombined[Calculated End Date] > MaxDate,
        TblCombined[(estimated) (billing) start date] <= MaxDate,
        TblCombined[PON/Other] IN { "PON", "Excluded" },
		ISBLANK( TblCombined[Currency] )
	)
	RETURN Result
)
VAR Result = ConvertedValueGBP + UnconvertedValue + ConvertedValueEUR
RETURN Result

Any improvement suggestions would be welcomed.  Thanks very much for all your help!