Calculate Bookings FX gap

[tomasjezek]

Hello,

I would like to crete a DAX measure for Bookings FX Gap. Example of the calculaton is in the table below:

	Beg. backlog	Shipments	End Backlog	Bookings	Bookings in USD	FX Gap
Coverage in orig. currency (i.e. JPY)	1,930,000	150,000	2,500,000	720,000	4,601	(194)
Coverage $	$12,293	$958	$16,129	$4,795
FX	0.006369427	0.006389776	0.006451613

Basically I have two columns for Coverage: the coverage in USD and the Coverage in invoice currency. I can calculate FX for different situations (Beginning backlog, Shipments, Ending Backlog), but for FX Gap I will need to use Avg. FX within concrete Fiscal Week period, but only the one from Shipments situation.

If in the specific Fiscal Week (FY-FW) period there is no Shipments, then use the following logic:

There might be some data issues in column 'Sales - Invoice Currency' esp. when Currency = JPY, when beginning and ending backlog in that currency are in USD, but Shipmnets is in JPY, but you can skip this issue.

Power BI File with data, bookings measures and pivot table in which the FX gap should be added is here:

https://upload.nolog.cz/download/91f8c14ca197503e/#_nHb0l8_EjmMKcDpnis5aA

Thank you.

Tomas

[v-xingshen-msft]

Hi @tomasjezek ,
I am sorry, as I am not a professional in calculating foreign exchange, I may not be able to understand your needs accurately, can you add some graphs of the results you are trying to get so that I can better solve your problem, here is my humble insight, if there is a shipment, use the exchange rate of the shipment, if there is no shipment, use the average of the exchange rate of the opening and closing backlogs to calculate and then finally summarize them, I look forward to your reply, I will get back to you as soon as I receive your message!

Measure = 
IF(
    'Calculations'[Shipments]>0,
    [Shipments]*[Avg FX_shipmnets],
   ([Beg.Backlog]*[Avg FX_beg.backlog]+[End.Backlog]*[Avg FX_end.backlog])/2)

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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