How do I Currency Convert All Values to Chosen Currency in Drop Down?

[julesdude]

Hi all,

I need a bit of help referencing a currency table and using the exchange rate provided in that table, against the relevant date, to convert the value to the currency the user has selected in a drop down on the report.

When this currency from the drop down is selected I need all the values in my table ('table A') changed using this exchange rate. The maybe tricky part is that for each row in Table A (each Asset listed in the row) which contains the Asset Value, displays the native currency value as standard as per in the model view the currency column in Table A. So there are several different native currencies listed/referenced, so the native currency against the asset needs to be looked up, this is then used in the currency conversion rates table against the date against for the converstion and used to adjust the value.

The added problem is that ECB only give a rate to Euros. So in all cased the amount will need to be converted to Euros and then out to the native currency again for situations where the asset's native currency is DKK , and the user has selected something like AUD for the value to be converted to. Example

Data subset from EBC in past 90 Days

<Cube time="2022-06-30">

      <Cube currency="DKK" rate="7.4392"/>

      <Cube currency="AUD" rate="1.5099"/>

</Cube>

[EUR -> EUR] * [EUR -> DKK]

[1/1.0000] * [7.4392] = 7.4392 (this one is simple as the rate matches that from the ECB extraction)

[AUD -> EUR] * [EUR -> DKK]

[1/1.5099] * [7.4392] = 4.9269

I've attached a sample file with this dummy data in for reference. There is an API connection to ECB to get the currency conversion rates for relevant currencies I need.

It's a bit meaty this one but any help at all I'd be grateful for. 

Sample File 

[julesdude]

@lbendlin 

Could it be done with LOOKUPVALUE ? The alternative I'm thinking of would be to create extra columns in Power Query against the dates in Table B - each column providing the currency conversion rate for a currency against that day. How do I do it?

[lbendlin]

Oh, you said the L word.  It was nice to have known you...

[julesdude]

@lbendlin  Ha yes I know.... 😁 not good huh? Well if there's another way....I just can't think of one 

[lbendlin]

We have an issue here. your currency table is not covering

We are missing the GBP to EUR rate for 2017-03-04, for example , and many others.

[lbendlin]

If your currency table were reliable we could use these measures: (note that the conversion measure could be optimized for scenarios where the selected currency matches the source currency)

Selected Currency := max(Currencies[Currencies])
converted :=
VAR SourcetoEUR =
    CALCULATE (
        MAX ( ECB_FX_RATES_to_DKK[OBS_VALUE] ),
        TREATAS (
            ROW (
                "CURRENCY", MAX ( TableA[Asset Native Currency] ),
                "TIME_PERIOD", MAX ( TableB[Date] )
            ),
            ECB_FX_RATES_to_DKK[CURRENCY],
            ECB_FX_RATES_to_DKK[TIME_PERIOD]
        )
    )
VAR conv =
    DIVIDE ( SUM ( TableB[Value] ), SourcetoEUR, 0 )
RETURN
    IF (
        [Selected Currency] = "EUR",
        conv,
        conv
            * CALCULATE (
                MAX ( ECB_FX_RATES_to_DKK[OBS_VALUE] ),
                TREATAS (
                    ROW ( "CURRENCY", [Selected Currency], "TIME_PERIOD", MAX ( TableB[Date] ) ),
                    ECB_FX_RATES_to_DKK[CURRENCY],
                    ECB_FX_RATES_to_DKK[TIME_PERIOD]
                )
            )
    )

[julesdude]

Hi @lbendlin 

Thanks for not deserting me 🙂 this is really helpful - thanks for helping me with the DAX.

Yes the conversion table is not so reliable for certain dates - so for weekends or any public holidays, there will not be a date entry to convert the currency. In those cases, best would be to refer just to the last date of conversion that can be found. So for example, if there is a value date for an asset that is datestamped on a Sunday, it would take the last known exchange rate data - most likely the Friday. 

The only other thing is to try and link all the DAX together to generate the values in Table A shown in the report.

Values are currently generated using the following two measures:

To get the last value for an asset in the timeframe shown (default is year):

ValueLastDate = Calculate(SUM(TableB[Value]), LASTDATE(TableB[Date]))

Then to get the Totals correctly adding the yearly amount, I refer to ValueLastDate as follows:

ValueLastDate SUM =

VAR __table = SUMMARIZE('TableA',[Asset Name],"__value",[ValueLastDate])

RETURN

IF(HASONEVALUE('TableA'[Asset Name]),[ValueLastDate],SUMX(__table,[__value]))

So I am not sure where in this logic I would fit in/refer your example above? I guess your conversion code would reference measure ValueLastDate as that needs to be calculated first. Then no need to use it again on the the totals row as that's just summing the already converted numbers using the measure ValueLastDate SUM. And ValueLastDate SUM is the measure used in the values section of the table visual?

[lbendlin]

Your ValueLastDate is too simplistic.  As you state you need to find the maximum date in the conversion table that is smaller or equal to your transaction date in Table B.

Here is a refactored version that first checks if the selected currency is the same as the source currency, then checks if the selected currency is EUR, and then handles all other cases.

converted =
VAR md =
    MAX ( TableB[Date] )
VAR mc =
    MAX ( TableA[Asset Native Currency] )
RETURN
    IF (
        mc = [Selected Currency],
        SUM ( TableB[Value] ),
        VAR mcd =
            CALCULATE (
                MAX ( ECB_FX_RATES_to_DKK[TIME_PERIOD] ),
                ECB_FX_RATES_to_DKK[TIME_PERIOD] <= md
            )
        VAR conv =
            DIVIDE (
                SUM ( TableB[Value] ),
                CALCULATE (
                    MAX ( ECB_FX_RATES_to_DKK[OBS_VALUE] ),
                    TREATAS (
                        ROW ( "CURRENCY", mc, "TIME_PERIOD", mcd ),
                        ECB_FX_RATES_to_DKK[CURRENCY],
                        ECB_FX_RATES_to_DKK[TIME_PERIOD]
                    )
                ),
                0
            )
        RETURN
            IF (
                [Selected Currency] = "EUR",
                conv,
                conv
                    * CALCULATE (
                        MAX ( ECB_FX_RATES_to_DKK[OBS_VALUE] ),
                        TREATAS (
                            ROW ( "CURRENCY", [Selected Currency], "TIME_PERIOD", mcd ),
                            ECB_FX_RATES_to_DKK[CURRENCY],
                            ECB_FX_RATES_to_DKK[TIME_PERIOD]
                        )
                    )
            )
    )

[julesdude]

@lbendlin thanks again. That certainly handles all cases of conversion I think! So in my original example, in the case where an asset currency is in DKK and my selected currency is to AUD the logic is:

<Cube time="2022-06-30">

      <Cube currency="DKK" rate="7.4392"/>

      <Cube currency="AUD" rate="1.5099"/>

</Cube>

(all these rates in the currency converstion table are to EUR)

[EUR -> EUR] * [EUR -> DKK]

[1/1.0000] * [7.4392] = 7.4392 

[AUD -> EUR] * [EUR -> DKK]

[1/1.5099] * [7.4392] = 4.9269

With frustration, I am still trying to work out how to reference the convert measure you supply into the other existing measures. Before the currency conversion issue, value dates were pulling into Table A perfectly:

Table A here - just to be clear - the row totals by Asset Name are simple the last value in the column time period. So for example Name1 here had the final value in 2020 as 23.00 in its native currency. Behind the scenes, the date that value came in must be used to lookup in the currency conversion table to get the rate to the selected currency on that day, if it is a different selection.
Fortunately we are not dealing with multiple dates that need to be summed - each value summed up needing to be converted on their date. It is just one unique value - the last in the time period shown.

Frustratingly I still can't work out how to link the values above to the convert measure you created. I tried replacing references to TableB[Value] in the convert measure to instead refer to the [ValueLastDate] measure, but to no avail. 

In regards to the weekend exchange rate absense of conversion rates, I could use a lastnonblank function to call the most recent date based on the TableB[Date] value datestamp. 

I've updated the pbix here, has convert measure in it, it's just not being referenced yet in the ValueLastDate SUM measure or ValueLastDate measure. 

[lbendlin]

My understanding is that Table A only provides the project currency. Is there more to that table?

I also don't understand what you still need the other measures (like [ValueLastDate]) for - their purpose (as I understand it) is covered by the convert measure.  Maybe rename it?

[julesdude]

I've renamed Table A in the visualisation just to be clearer and so it doesn't share the name of Table A in the model:

Table A in the model provides Currency and Country - i merge the Country column of Table A to Table B by Asset Reference, just to make things simpler for other visualisations. 

[ValueLastDate] was a measure I used to just bring in the last values without currency conversion. It is referenced by the measure [ValueLastDate SUM] which itself tries to get correct grand totals in Asset Value table (above)

Yes all other measures are redundant, except for MMeasure which is used in the other visualisations to get them functioning and references Measure New:

Measure New =

VAR _0 = LASTDATE ( 'TableB'[Date] )

VAR _1 = CALCULATE ( LASTDATE( 'TableB'[Date] ) , ALLEXCEPT ( 'TableB' ,'TableB'[Country] ,'TableB'[Asset Reference] ) )

VAR _2 = IF ( _1 = _0 , SUM ('TableB'[Value] ) , BLANK() )

RETURN

_2

MMeasure =

VAR __table = SUMMARIZE('TableB',[Date],"__value",[Measure New])

RETURN

IF(HASONEVALUE(TableB[Date]),[Measure New],SUMX(__table,[__value]))

When I use the converted measure in the Asset Value table and select AUD as currency to convert to, I just get this:

[lbendlin]

The conversion only works on day level.  If you need this on a year level you need to refactor the measure to use iterator functions across all dates of that year.

[julesdude]

Ahhh...I thought this would be simpler somehow, apologies. When I produce this table using the [ValueLastDate SUM] measure in the visualisation's Values area, which references the [ValueLastDate] measure, it gives me the correct values I'm after - the last value in that year, or, because I have a date hierachy, the last value in the quarter, or last value in the month, if I drill down.  I was hoping that at whatever point it takes that value it can also obtain the date that value was assigned from the same row it is referencing that value in TableB, using both value and date to run a lookup on the conversion table to start making the conversion, returning the converted output back into this Asset Value table here.

Basically whatever value shows here, i need converted instead of showing what it does now which is a value in the native currency and not converted to the currency selected in the dropdown. I was just hoping that as i can give the correct unconverted values here it can also apply additional logic at the time to lookup the conversion rate for that date and convert the number.

[lbendlin]

No matter what you look at in the visual (month, quarter or year), deep down you still need to compute the conversion for each single row before you can aggregate it back to the level you are looking at. 

[julesdude]

Yes. Sadly I think it is beyond my level of understanding to apply. 

I think I could probably try an easier approach where i create new columns in Table B that each correspond to a currency (I'm only ever going to have about 6 available for selection) and for each row where there is a value, use the date on that row to obtain the currency converstion to EUR. This could then be used maybe in conjunction with a DAX SWITCH statement to reference the correct column based on the dropdown currency selection, and the conversion is already populated and ready for calculation.

[lbendlin]

I think it can be done, but it's certainly not trivial.  Will take a while to think about and test.

[lbendlin]

Why has Table A a Date column?  Why has Table B a Country column?  Are you planning to use daily exchange rates? Have you considered adding a calendar table?

[julesdude]

Hi @lbendlin 

Yes, I've now added the calendar table and updated the file. 

In the data model:

Table A and B are a simplified example, but each of these two tables are separate API calls to a system so the data comes in with this information. Table A has a Date column because it is a As Of Date date stamp. Same as the Date in Table B. Table B doesn't initially have a country column like Table A but i merge query from Table A to B linked by Asset Reference so that it's easier to make references to country all in the one Table B. 

And yes I'm planning on using daily exchange rates. Essentially the time slicer here - the 'to' date would become the today's date if adjusted, and whatever that date might be is the lookup date on the converstion table to begin making the conversion to the chosen currency in the currency drop-down. This would change whatever values are showing in the top left table on the report canvas.

New file attached here: 

[julesdude]

Just been trying out some other ways of doing this and looking at this article by Maco Russo. Currency conversion in Power BI reports - SQLBI

I guess that this comes close although the problem is that this is to convert one currency into another, whereas in my data, there are assets which have multiple currencies so as it goes down the list of them, it needs to find the assigned currency to that asset in the table that has the currency next to the asset, then look up the in another table - the currency rate conversion and date - the rate to convert to, to get it into EUR, then using the same table finding the converstion rate against the same date to the target currency. That's the logic that needs to be followed, I just don't know if it is possible.