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 ba...
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.

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.

      • Lodan's avatar
        Lodan
        Helper II
        1 year ago

        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?

    • Lodan's avatar
      Lodan
      Helper II
      1 year ago

      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!