Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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
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
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.
Hello,
I Experienced this error when I try to create relationship
Hi @sclencioco
It seems you need to update the version. Then set the relationship. Here is the sample file for your reference.
Regards,
Cherie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |