sum of values with different currency

[sclencioco]

Hello,

We have to compute for the grand total amount, however, the data contains two different currencies

we have a list of values to convert USD to PHP (exchange_rate_id is 1) but we need to capture the latest conversion per month

can I ask for help for the formula. Thank you.

[v-cherch-msft]

Hi @sclencioco

You may get the latest conversion per month by below calculated column or measure:

LatestRate =
VAR RankForDate =
    RANKX (
        FILTER (
            Rates,
            YEAR ( Rates[covered_date] ) = YEAR ( EARLIER ( Rates[covered_date] ) )
                && MONTH ( Rates[covered_date] ) = MONTH ( EARLIER ( Rates[covered_date] ) )
        ),
        Rates[covered_date],
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( Rates[exchange_rate_id] = 1 && RankForDate = 1, Rates[rate] )

Measure =
VAR RankForDate =
 RANKX (
 FILTER (
 ALL ( Rates ),
 YEAR ( Rates[covered_date] ) = YEAR ( MAX ( Rates[covered_date] ) )
 && MONTH ( Rates[covered_date] ) = MONTH ( MAX ( Rates[covered_date] ) )
 ),
 CALCULATE ( MAX ( Rates[covered_date] ) ),
 ,
 DESC,
 DENSE
 )
RETURN
 IF (
 MAX ( Rates[exchange_rate_id] ) = 1
 && RankForDate = 1,
 MAX ( Rates[rate] )
 )

Regards,

Cherie

[sclencioco]

Hello,

I was able to get the latest per month, however, how can I get the converted amount in PHP?

here's the table of conversion

Below is sample amount (in USD) to be converted to PHP

[v-cherch-msft · Accepted solution]

Hi @sclencioco

You may add a month column for your rate table. Then link the Rate table with the Amount Table in relationship view. Then you may create a measure such as:

Convert =
CALCULATE (
    SUM ( Amount[Amount] ) * MAX ( Rates[LatestRate] ),
    FILTER ( ALL ( Amount ), Amount[Month] = MAX ( Amount[Month] ) )
)

Regards,

Cherie

[oslosa]

Hi, there are multiple solutions to the currency conversion problem online, but none seem to get the sumtotal right, including this solution. Do you know how to solve it? I suppose it would contain some combinations of IF(hasonevalue()) and sumx.

[sclencioco]

Hello,

I Experienced this error when I try to create relationship

[v-cherch-msft]

Hi @sclencioco

It seems you need to update the version. Then set the relationship. Here is the sample file for your reference.

Regards,

Cherie