Exchnage rate issue

Question

I have created two measure for exchange rate. One of them is Total Income Transferred - Actual. This works fine when I select any date from dim exchange date. The value calculated is correct.

Total Income Transferred - Actual =

VAR Target_Value =

CALCULATE (

SUMX (

Current

Actual[Total Income transferred lc]

/ LOOKUPVALUE (

'Dim Exchange Rate'[Rate],

'Dim Exchange Rate'[Currency], Actual[LocalCurrrency],

'Dim Exchange Rate'[Date_Sorted], SELECTEDVALUE ( 'Dim Exchange Date'[Date] )

)

RETURN

IF (

HASONEVALUE ( 'Dim Exchange Date'[Date_Key] ),

Target_Value,

SUM ( Actual[Total Income transferred] )

)

But I have another measure based on same logic but here I am taking lastnonbank value for the measures. But the exchange rate applied is wrong. Can you please help here.

Operational Cost - Report =

VAR LASTNONBLANK_1 =

CALCULATE (

SUM ( Report[Operational Cost] ),

LASTNONBLANK (

'Dim Period'[PeriodDate],

CALCULATE ( SUM ( Report[Operational Cost] ) )

)

VAR Target_Value =

CALCULATE (

SUMX (

Report,

[Operational Cost lc - Report]

/ LOOKUPVALUE (

'Dim Exchange Rate'[Rate],

'Dim Exchange Rate'[Currency], Report[LocalCurrency],

'Dim Exchange Rate'[Date_Sorted], SELECTEDVALUE ( 'Dim Exchange Date'[Date] )

)

RETURN

IF (

HASONEVALUE ( 'Dim Exchange Date'[Date_Key] ),

Target_Value,

LASTNONBLANK_1

)

Thanks

Manoj

amitchandak · Answer

kumarm , Can you share sample data and sample output in table format? 
&nbsp;
refer if these can help
https://www.youtube.com/watch?v=4dosxfNxR6Mhttps://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/https://radacad.com/currency-converter-power-bi-function-part-1https://www.youtube.com/watch?v=Q4hF4CwJsFEhttps://radacad.com/currency-exchange-rate-conversion-in-power-bi-with-live-rates-part-2https://businessintelligist.com/2015/03/17/power-bi-tutorial-how-to-implement-currency-conversion-using-power-query-and-power-pivot/https://blog.beyondimpactllc.com/blog/building-a-dynamic-currency-converter-with-power-bihttps://www.youtube.com/watch?v=9oPFgHvGyKchttps://blog.enterprisedna.co/how-to-download-latest-exchange-rates-into-your-power-bi-model/https://radacad.com/currency-converter-power-bi-function-part-1https://radacad.com/currency-exchange-rate-conversion-in-power-bi-with-live-rates-part-2

kumarm · Answer

Dear Amit,Thanks for you qucik response.I have gone through those link but it doesnot work for me.I have sent you pbix file in personal mesage.I would appreciate If you could help me on this.&nbsp;ThanksManoj&nbsp;

anonymous · Answer

Hi&nbsp;kumarm,
Can you please share some dummy data to test? It should help troubleshoot and test the formula.&nbsp;
How to Get Your Question Answered Quickly&nbsp;
In addition, it seems like a common measure multiple aggregate calculation issues, please take a look at following blog about measure aggregation on the total level:
Measure Totals, The Final Word&nbsp;Regards,
Xiaoxin Sheng

kumarm · Answer

Dear&nbsp;Greg_Deckler&nbsp; ,&nbsp;&nbsp;The issue is is applying exchange rate.In my fact table Report,I have lowest granularity as quarterkey.Which is combination of year and Quarter number.But exchange rate,I have for every date.So when I select the date from dim exchange date ,this date has one exchange date,but when it goes for lookup in Fact_report,it has lowest granularity as quarter key.So here exchnage rate is multiple in this case and getting summed up.Which create a problem.So I want only one exchnage rate against each currency from the lookup.&nbsp;Note:-My dim_period is not normal date dimension table.I mean here the lowest granularity is not date but combination of year and month which is called as perioddate.This date in linked to real date dimension table.&nbsp;Total Income transferred - Report = VAR LASTNONBLANK_1 =&nbsp;&nbsp;&nbsp; CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM ( Report[Total Income transferred] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LASTNONBLANK (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Dim Period'[PeriodDate],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE ( SUM ( Report[Total Income transferred] ) )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp; )VAR Target_Value =&nbsp;&nbsp;&nbsp; CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUMX (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Report,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Total Income transferred lc - Report]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; / LOOKUPVALUE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Dim Exchange Rate'[Rate],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Dim Exchange Rate'[Currency], ('Report'[LocalCurrency]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Dim Exchange Date'[Year],values('Dim Period'[Year]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Dim Exchange Rate'[Date_Sorted],SELECTEDVALUE ( 'Dim Exchange Date'[Date] )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp; )RETURN&nbsp;&nbsp;&nbsp; IF (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; HASONEVALUE ( 'Dim Exchange Date'[Date_Key] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Target_Value,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LASTNONBLANK_1&nbsp;&nbsp;&nbsp; )&nbsp;The above logic works fine in another fact table where I have lowest&nbsp;granularity map between fact table and dim_period.Please find test model in below link.https://drive.google.com/file/d/1Y9Vuwcq20rV5kWcmNZqtwsRk2gNlwtwl/view?usp=sharing&nbsp;ThanksManoj&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;kumarm,
In fact, since power bi data mode table not existed row/column index. So if you use lastnonblank function and try to pick up the 'last' records, it may not return the result you wanted. You can take a look at the below blog to know more about this function.
 LASTNONBLANK Explained&nbsp;
As you said it has rolled up your records and get the wrong result, what Target_Value&nbsp;value you wanted? AFAIK, power bi dax formula contains a few functions that include iterator feature, if you not wan to rolling up the records, you can consider using other functions instead sumx.Regards,Xiaoxin Sheng

Forum Discussion

Exchnage rate issue

6 Replies

Related content

Utilization Rate

Currency FX Rates

Averagex issue

Adding an intererst rate

Exchange Rates - Historical