Trial Balance Conversion from local currency to USD

DAX DAX Calculations
1

Hi

I’ve been creating a set of DAX formula to convert a YTD TB in local currency to a YTD TB in USD. I am as far as converting to USD but am struggling creating a line for Currency Translation Adjustment (CTA). The CTA as a calculation is in two parts:
1 Difference between specified rates of Income Statement and Balance Sheet
2 Difference between fixed amounts and Balance Sheet Values

For part 1 I have the calculation but it is present over all three divisions when I look at “Business Unit”. This will be because I don’t have a direct link between my mapping file and my file showing business unit description.

For Part 2 I need a one line showing the total of the difference between all fixed accounts by company name

Below is the image of my data set.

Excel file 400_Full TB (Test) is how the final report numbers should look. I’ve also added a tab to show the breakdown of CTA.

image
image833×771 29.9 KB

Financial Results_Conversion.pbix (233.0 KB)
400_Full TB (Test).xlsx (52.9 KB)

Regards

2

Hi @olson.michele - Is this issue still pending?

3

Hi Ankit

Still working through another issue relating to using a lookup to put exchange rates against nominal codes. As the lookup is to cumulative trial balance it is missing period adjustments when the cumulative balance is zero, therefore not pulling the rate across. I needed a rate for multiplying if I have a period currency movement. If you have any ideas to resolve this I would appreciate it.

Regards
Financial Results_Conversion.pbix (238.7 KB)
400_Full TB (Test).xlsx (67.8 KB)