Measure not providing any Total

[Anonymous]

Hi,

I have an exchange rate master in my model, with following columns:

• Conversion Date
• Currency From
• Currency To
• Conversion Rate

There is a Date table with specific period measures like Fiscal Year, Month Year, Quarter, etc. linked to key date columns of other tables.

And it is linked to my main Accounting table which has following columns:

• Functional/Transactional Currency Code
• Functional/Transactional Currency Amount
• Home/Reporting Currency Code
• Home/Reporting Currency Amount
• Transaction Date
• Etc.

Then there are measures created for customized Accounting requirements like:

• FX Amount (For the Period) - Which works on Functional/Transactional Currency Amount column
• Reporting Amount (For the Period) - Home/Reporting Currency Amount
• Etc.

The FX Amount (For the period) gives the Amount in Functional Currency and Reporting Amount (For the Period) gives in Reporting Currency. My requirement is to create a Measure similar to FX Amount (For the period) but which gives the amount in an intermediate currency (based on selection, but default "USD" if no specific currency is selected). The applicable rates for Functional Currency to Intermediate Currency are uploaded in Exhange Rate Master, we need to pick the last rate that is available in the exchange rate master for the selected period.

I have created the following measure which works fine if my report is at Functional Currency Code level (lowest). But summation at any other level (month/year/entity/etc.) gives a blank output.

Translated Amount (For the period) =

Var _CurrFrom = SELECTEDVALUE(Accounting[Functional Currency Code])

Var _CurrTo = if (ISBLANK(SELECTEDVALUE('Exchange Rate Master'[Currency To])),"USD",SELECTEDVALUE('Exchange Rate Master'[Currency To]))

Var _Date = CALCULATE(max('Exchange Rate Master'[Conversion Date]),'Exchange Rate Master'[Currency From]=_CurrFrom,'Exchange Rate Master'[Currency To]=_CurrTo)

Var _Rate = LOOKUPVALUE('Exchange Rate Master'[Conversion Rate],'Exchange Rate Master'[Currency From],_CurrFrom,'Exchange Rate Master'[Currency To],_CurrTo,'Exchange Rate Master'[Conversion Date],_Date)

Var _FinRate = if(_CurrFrom=_CurrTo,1,_Rate)

Var _OP = if(HASONEVALUE(Accounting[Functional Currency Code]),

    [FX Amount (For the period)]*_FinRate,

    sumx(VALUES(Accounting[Functional Currency Code]),[FX Amount (For the period)]*_FinRate)

    )

Return _OP

I will not be able to attach pbix or any other files due to org restrictions.

Please help!

Thanks in advance,

Shailee

[Anonymous]

I have achieved the measure total with following measure:

Translated Amount (For the period) =

Var _CurrFrom = SELECTEDVALUE(Accounting[Functional Currency Code])

Var _CurrTo = if (ISBLANK(SELECTEDVALUE('Exchange Rate Master'[Currency To])),"USD",SELECTEDVALUE('Exchange Rate Master'[Currency To]))

Var _Date = CALCULATE(max('Exchange Rate Master'[Conversion Date]),'Exchange Rate Master'[Currency From]=_CurrFrom,'Exchange Rate Master'[Currency To]=_CurrTo)

Var _Rate = LOOKUPVALUE('Exchange Rate Master'[Conversion Rate],'Exchange Rate Master'[Currency From],_CurrFrom,'Exchange Rate Master'[Currency To],_CurrTo,'Exchange Rate Master'[Conversion Date],_Date)

Var _FinRate = if(_CurrFrom=_CurrTo,1,_Rate)

Var _Table =

SUMMARIZE(Accounting,Accounting[Functional Currency Code],Accounting[Effective Date],

"FXAmt",[FX Amount (For the period)],

"Rate",if(Accounting[Functional Currency Code]=_CurrTo,1,

LOOKUPVALUE('Exchange Rate Master'[Conversion Rate],'Exchange Rate Master'[Currency From],Accounting [Functional Currency Code],'Exchange Rate Master'[Currency To],_CurrTo,

'Exchange Rate Master'[Conversion Date],Accounting[Effective Date])))

Var _OP = if(HASONEVALUE(Accounting[Functional Currency Code]),

[FX Amount (For the period)]*_FinRate,

sumx(_Table,[FXAmt]*[Rate])

)

Return _OP

But it works only for the dates where intermediate exchange rates are available. How can I make it work basis the last rate available in the exchaange rate master?

Thanks in advance,

Shailee

[Anonymous · Accepted solution]

After some iterations, the final measure that worked:

Translated Amount (For the period) =
Var _CurrTo = if (ISBLANK(SELECTEDVALUE('Exchange Rate Master'[Currency To])),"USD",SELECTEDVALUE('Exchange Rate Master'[Currency To]))
Var _Table =
SUMMARIZE(Accounting,Accounting[Functional Currency Code],'Date'[Month Year],
"FXAmt",[FX Amount (For the period)],
"Rate",if(Accounting[Functional Currency Code]=_CurrTo,1,
CALCULATE(LASTNONBLANK('Exchange Rate Master'[Monthly Average Conversion Rate],1),FILTER('Exchange Rate Master','Exchange Rate Master'[Currency From]=Accounting[Functional Currency Code] && 'Exchange Rate Master'[Currency To] = _CurrTo && 'Exchange Rate Master'[Conversion Date]<=max('Date'[Date])))))
Var _OP = sumx(_Table,[FXAmt]*[Rate])
Return _OP