Forum Discussion

kumarm's avatar
kumarm
Frequent Visitor
6 years ago

Exchnage rate issue

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...
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi kumarm,

Can you please share some dummy data to test? It should help troubleshoot and test the formula. 

How to Get Your Question Answered Quickly 

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 
Regards,

Xiaoxin Sheng

kumarm's avatar
kumarm
Frequent Visitor
5 years ago

Dear   ,

 

 

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.

 

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.

 

Total Income transferred - Report =
VAR LASTNONBLANK_1 =
    CALCULATE (
        SUM ( Report[Total Income transferred] ),
        LASTNONBLANK (
            'Dim Period'[PeriodDate],
            CALCULATE ( SUM ( Report[Total Income transferred] ) )
        )
    )
VAR Target_Value =
    CALCULATE (
        SUMX (
            Report,
            [Total Income transferred lc - Report]
                / LOOKUPVALUE (
                    'Dim Exchange Rate'[Rate],
                    'Dim Exchange Rate'[Currency], ('Report'[LocalCurrency]),
                    'Dim Exchange Date'[Year],values('Dim Period'[Year]),
                    'Dim Exchange Rate'[Date_Sorted],SELECTEDVALUE ( 'Dim Exchange Date'[Date] )
                )
        )
    )
RETURN
    IF (
        HASONEVALUE ( 'Dim Exchange Date'[Date_Key] ),
        Target_Value,
        LASTNONBLANK_1
    )

 

The above logic works fine in another fact table where I have lowest 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

 

Thanks

Manoj

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi 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 

    As you said it has rolled up your records and get the wrong result, what Target_Value 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

    • kumarm's avatar
      kumarm
      Frequent Visitor
      5 years ago

      I still have the issue.Can anybody help me on this.