How to get rate from last available month

[DJ55]

Hi Everyone,

I am trying to calculate the price for PO. 

so my formula will be   Net price*exchange rate 

I have table for exchange rate in below format

I have crated association between calendar month and  PO generated month .

I want to create an expression(not in script) if  we have a PO for the month of May/2016 and exchange rate is not available in exchange rate table, it should pick exchange rate of last available month . In this case it is Apr/2016.

I tried creating variable with firstsortedvalue but when I tried inserting that variable in other sum function it showing nested aggregation not allowed.

if(wildmatch(CADtoUSDExchangeRate,num(CADtoUSDExchangeRate))=1,CADtoUSDExchangeRate,FirstSortedValue(CADtoUSDExchangeRate,-CalendarMonth)))

can anyone help me with this?

[rubenmarin]

Hi, it would be better if you solve this in script, I read that you want an expression but don't know why, the steps would be:

- Create a calendar with all year-month from the min value loaded to the max. ie:

LOAD 
	 Date(AddMonths(MinDate, IterNo()-1),'MMM/YYYY')	as YearMonth
While AddMonths(MinDate, IterNo()-1) <= MaxDate
;
LOAD 
	 MonthStart(Min(FieldValue('DateField', RecNo()))) 	as MinDate,
	 MonthStart(Max(FieldValue('DateField', RecNo()))) 	as MaxDate
AutoGenerate FieldValueCount('DateField');

- Join the CADtoUSDExchangeRate with this table to populate infomred months.

- LOAD resident from this table, sort by the oldest date and use Peek() to reteive the last value when a YearMonth doesn't has any value

LOAD
  YearMonth,
  If(IsNull(CADtoUSDExchangeRate), Peek(CADtoUSDExchangeRate), CADtoUSDExchangeRate) as CADtoUSDExchangeRate
Resident table
Order By YearMonth;

[DJ55]

Hi Ruben 
Thanks for the response I am using Data Manager and not able to work with script.
Is there any way to do in  variable or Master measure?