Hi,
My struggles continue with getting a correct column measure total from my matrix table into a card visual.
The column measure, which displays the correct monetary values:
Valuation to Fund Currency (Last Date) =
// CONVERT THE NATIVE CURRENCY AMOUNT TO THE FUND CURRENCY
// Get user selected currency
var selectedcurrency = [var FundCurrency]
// Get native currency
var origincurrency = [var origincurrency]
// Get last date asset was valued
var dateofassetevaluation = LASTDATE('Asset_Valuation'[Valuation Date])
// Look for last date of valuation and return nearest preceding date available for currency converstion in adjacent column [ECBRateDate] in the DataTable table
var ecbdateforevaluation = LOOKUPVALUE(DateTable[ECBRateDate],DateTable[Date],dateofassetevaluation)
// Now return the conversion rate value [OBS_VALUE] in the ECB_FX_RATES table where [CURRENCY] matched origincurrency and [CURRENCY_DENOM] is EUR and date is the same [TIME_PERIOD] = ecbdateforevaluation
var closest_to_date = CALCULATE(MAX(DistinctECBRates[TIME_PERIOD]), DistinctECBRates[TIME_PERIOD] <= dateofassetevaluation, ECB_FX_RATES[CURRENCY] = origincurrency)
var currency_value_closest_to_date = CALCULATE(SUM(ECB_FX_RATES[OBS_VALUE]), ECB_FX_RATES[TIME_PERIOD] = closest_to_date && ECB_FX_RATES[CURRENCY] = origincurrency)
// LOOKUPVALUE(ECB_FX_RATES[OBS_VALUE],ECB_FX_RATES[CURRENCY],origincurrency,ECB_FX_RATES[CURRENCY_DENOM],"EUR",ECB_FX_RATES[TIME_PERIOD],ecbdateforevaluation)
// Now calculate the conversion rate - if the origincurrency is EUR, just use the [ValueLastDate SUM] value otherwise if it's different divide the [ValueLastDate SUM] with currency_value_closest_to_date converstion rate
var euro_value = if(origincurrency="EUR",[Valuation Total TABLE SUM],[Valuation Total TABLE SUM]/currency_value_closest_to_date)
// Get the fund currency rate by finding the currency out of EUR to selected currency - return the rate [OBS_VALUE] in ECB_FX_RATES table by matching [CURRENCY] with the selectedcurrency & [CURRENCY_DENOM] = EUR and [TIME_PERIOD] = ecbdateforevaluation (the date of the valuation)
var closest_to_date2 = CALCULATE(MAX(DistinctECBRates[TIME_PERIOD]), DistinctECBRates[TIME_PERIOD] <= dateofassetevaluation, ECB_FX_RATES[CURRENCY] = selectedcurrency)
var desired_currency_value_closest_to_date = CALCULATE(SUM(ECB_FX_RATES[OBS_VALUE]), ECB_FX_RATES[TIME_PERIOD] = closest_to_date2 && ECB_FX_RATES[CURRENCY] = selectedcurrency )
// var desired_currency_value_closest_to_date = LOOKUPVALUE(ECB_FX_RATES[OBS_VALUE],ECB_FX_RATES[CURRENCY],selectedcurrency,ECB_FX_RATES[CURRENCY_DENOM],"EUR",ECB_FX_RATES[TIME_PERIOD],ecbdateforevaluation)
// If selected currency is EUR, use the euro_value amount as no second stage conversion needed, otherwise use the euro_value * desired_currency_value_closest_to_date to convert to selected currency. If the calculation returns an error, return conversion amount as 0
var actual_value = IFERROR(if(selectedcurrency="EUR",euro_value,euro_value* desired_currency_value_closest_to_date),0)
RETURN
IF (NOT origincurrency = "EUR", actual_value * [Ownership Percentage], actual_value)
Essentially the above takes the last dated monetary value of a asset (building) and converts this amount to another currency if necessary. In my card visual, I use the following which takes the above measure and wraps it up in a SUMX. It comes close, but gives an incorrect figure:
Total Valuation = SUMX(Building, [Valuation to Fund Currency (Last Date)])
How can I get the correct total?
