Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Translated value calculation with currency rate on separate table

Hi

 

I would like to calculate the closing balance translated reporting currency (ZAR in this case) from the base currencies using a DAX measure.  My current attempt wont work. 


My Fact table has a column with the currencyID and the currency rates are stored on a separate dimension table. So there is a relationship between the Fact table and the Dimension table.

I believe my code is wrong ito getting the rate and applying it to the base movement values. I am also not sure if and how to apply the RELATED function if needed.
The intended logic is:
(cumulative base value raised less basevalue allocations/"receipts") * currency rate of selected reportingperiod

 

My current attempt to calculate the closing balance at each selected reporting period date:

 

Debtors Close ZAR:=
VAR BaseValueRaisedEntries =
CALCULATE(SUMX('FactClaimsRIPaid',
('FactClaimsRIPaid'[OpenMarketFacClaimsPaid]+'FactClaimsRIPaid'[OpenMarketFacClaimsPaidVAT])
* MAX( 'DIM_CurrencyRates'[Rate] )),
FILTER('DIM_AccountingPeriod', 'DIM_AccountingPeriod'[ReportingDateEnd] <= MAX('DIM_AccountingPeriod'[ReportingDateEnd]) ) )

VAR BaseValueAllocationsEntries =
CALCULATE(SUMX('FactRIClaimsRecovered',('FactRIClaimsRecovered'[OpenMarketFacClaimRecoveryAmount]+'FactRIClaimsRecovered'[OpenMarketFacClaimRecoveryAmount])
* MAX( 'DIM_CurrencyRates'[Rate] )),
FILTER('DIM_AccountingPeriod','DIM_AccountingPeriod'[ReportingDateEnd] <= MAX('DIM_AccountingPeriod'[ReportingDateEnd]) ) )

RETURN RaisedEntries-AllocationsEntries

 
you have helped me before where I had the currency rate in the fact table

8 Replies