Currency help based on Selection

[amit_saini]

Hi Folks,

I'm having currency Table like below:

Plant	Jan	Feb	Mar	Apr
A	0.0123	0.0124	0.0125	0.0126
B	1	2	3	4
C	0.1	0.2	0.3	0.4

For Plant A exchange rates are .0123 for Jan , .0124 for Feb and so on...

Now if I select any one Month , then my result is working fine.

Suppose I select Multiple Months say Jan , Feb , Mar

Then Revenue should be multiply with Mar Exchange Rate, in case if I  select Mar and Apr together then exchange rate should be from Apr.

So we have to take always the latest Month as per selection.

I'm not sure ,how this can be achieved , please help!

My expression is = Sum(Revenue)*Exchange_Rate

Regards,

Amit

[sunny_talwar]

And it does match.... I am not sure how can I see 10613 number in the attached app

[sunny_talwar]

What is the number you see for this

=Sum([Net sales without tooling])

Do you get 14709.383?

[amit_saini]

Yes When it for Jan Feb and March

But If u just select Jan And Feb this Sum([Net Sales...]) is 10020.29089 , so the right output should be 10613.993125233 and not 15580.91470....

Thanks,

AS

[sunny_talwar · Accepted solution]

Hahahaha okay... use this expression

Sum([Net sales without tooling]) * FirstSortedValue(Exchange_Rate, -Sort_Month)

Ignored selection in Month field for the Sum expression... I didn't know how to check with your numbers after making selections. You should have been able to fix that.... Anyways see if this works

[amit_saini]

Select Jan and Feb here than you will come to know the issue

[amit_saini]

Thanks A lot Buddy!

At least i got some idea now , i just need to fix the issue if u are selecting just 1 month , as of now nothing is coming in table:

Thank u so much!

[sunny_talwar]

Try this

Sum([Net sales without tooling]) * FirstSortedValue(DISTINCT Exchange_Rate, -Sort_Month)

[amit_saini]

Super!