Forum Discussion

Lodan's avatar
Lodan
Helper II
1 year ago
Solved

Using exchange rates

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

Need Help
  • johnt75's avatar
    johnt75
    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
  • johnt75's avatar
    johnt75
    1 year ago

    The line

    TblCombinedx[Currency] = Source

    makes sure that it is the correct currency.

6 Replies

  • johnt75's avatar
    johnt75
    Super User
    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
    • Lodan's avatar
      Lodan
      Helper II
      1 year ago

      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

      • johnt75's avatar
        johnt75
        Super User
        1 year ago

        The line

        TblCombinedx[Currency] = Source

        makes sure that it is the correct currency.