Need to calculate custom FX within table

[eddibu]

Hello, could you please help me?

link to sample pbix: for question.pbix

I have two tables [Product ALL] and [FX Rates daily], they have relationship 'Currency'. 

I need to perform following calculation for computing custom FX translation difference:

1. From [Product ALL] take only 'Income' (daily for each currency) in 'amount_orig_currency'. Divide them by average 'FX2' from [FX Rates daily] rate (average for the month for each corresponding currency). 

2. Find corresponding Expense for each 'Income'  for each corresponding day and currency in 'amount_after_exchange'

3. ('Income' minus  'Expense' for corresponding day and currency in 'amount_orig_currency') divide by 'FX2' for each corresponding currency on the date of 31 March (last day of the month)

4. Final result FX= 1-2-3 (Value of step 1 minus value of step 2 minus value of step 3)

[eddibu]

Hi @v-yilong-msft ,

Could you please clarify and send more details about what you need ? My third point is not understandable?

Regards,

Ed

[v-yilong-msft]

Hi @eddibu ,

I opened your .pbix file and tried to read the first two steps, but I'm stuck on the third step, if possible, could you simply calculate some data or convert it to make it easier for me to understand?

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.