Forum Discussion
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
v-kkf-msft you have helped me before where I had the currency rate in the fact table
8 Replies
- AnonymousNot applicable
parry2k
Anonymous
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-on-selected-date/m-p/696210#M696 - AnonymousNot applicablethis 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]))) - v-yalanwu-msft
Community Support
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. - v-yalanwu-msft
Community Support
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. - AnonymousNot applicable
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=100847676985930671437&rtpof=true&sd=true- v-yalanwu-msft
Community Support
Hi, Anonymous ;
Sorry i don't have access,
Best Regards,
Community Support Team_ Yalan Wu- AnonymousNot applicable
hi
I have re-shared with you.