Translated value calculation with currency rate on separate table

[Anonymous]

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

v-kkf-msft you have helped me before where I had the currency rate in the fact table

[Anonymous]

Hi @v-yalanwu-msft 

See attached some sample data. 
https://drive.google.com/file/d/1xg8HS5vLGFDPFMnojm8c3V0cz5TSopgd/view?usp=sharing

And here is an example of the output I would like. 

https://docs.google.com/spreadsheets/d/1_s0lYvVF0BGqFFmvglfyHqAeCnGCL3vx/edit?usp=sharing&ouid=10084...

[v-yalanwu-msft]

Hi, @Anonymous ;

Sorry i don't have access,

Best Regards,
Community Support Team_ Yalan Wu

[Anonymous]

Hi there

I reshared. Please can you check it?

[Anonymous]

hi

I have re-shared with you.

[v-yalanwu-msft]

Hi, @Anonymous ;

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @Anonymous ;

Can you share simple files after removing sensitive information? And the desired output result for better understanding?

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

this was the measure that worked where the currency rate was stored on the fact table

Measure =

CALCULATE(

SUM( 'SSAS V_FactPremiumCombined'[GrossDebtors_MovementInclVAT] )

* MAX( 'SSAS V_FactPremiumCombined'[ClosingCurrencyRate] ),

FILTER(

'SSAS V_FactPremiumCombined',

'SSAS V_FactPremiumCombined'[Month] <= MAX('SSAS V_DIM_Date'[Date])

)

)

[Anonymous]

@parry2k 
@CMarsh 

what was the final DAX pattern to get the right rate for each selected month in your posting in the link below?
My issue above is similar.

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Translate-foreign-currency-transactions-based...