locked
Measure to calculate alternative Reporting Currency RRS feed

  • Question

  • Hi Friends,

    I have a questions while calculating Measures in Tabular Model.

    Below is a sample of our data model.

    We have ExchangeRate table where Reporting Currencies are either AUD or NZD.

    We have Sales table where sale is captured in local transaction currency i.e. SIN - singapore & CAD- Canadian dollars. Reporting currenct is always captured in AUD dollars.

    While reporting use will select a specific date from RunDate table and then report should return

    - Date of Sale

    - ProductID

    - AUDBalance - Reporting Balance in AUD (whcih is straight as is already available in Sales table)

    - NZDBalance - This we need to calculate, we have AUD to NZD rate in Exchange table (Columnd D5, D8 & D11) so for a selected day we will calculate AUDBalance value from Sales table with that days ExchangeRate (AUD - NZD).

    - LocalCurrencyBalance - Traded Currency Balance (which is straight as is already availabe in Sales table)

    I tried to create a measure like below:

    NZD_Rate:=CALCULATE(VALUES(ExchangeRate[ExRate]),FILTER(ExchangeRate,Sales[Date]=ExchangeRate[Date] && ExchangeRate[BaseCurrency]="AUD" && ExchangeRate[ReportingCurrency]="NZD"))

    This is not working as when this filter goes in it gets 3 different values (for 15,16 & 17 Feb) so I used CALCULATETABLE as well but its giving warning/error:

    Warning    1    Measure 'Sales'[NZD_Rate] : The value for column 'Date' in table 'Sales' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.   

    Please correct me what I am doing wrong here.

    Regards

    Gurpreet Sethi


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    Monday, February 20, 2017 9:17 PM

All replies

  • Hi Gurpreet,

    In your scenario, you may try to use LOOKUPVALUE to get NZD_Rate.

    NZD_Rate:=LOOKUPVALUE('Sales'[Date],'Sales'[LocalTranCCYUD],'Sales'[ReportingCurrency],values(ExchangeRate[ExRate]))

    For more detail, you can also refer to blog Currency Conversion in Tabular Model Using DAX: http://salvoz.com/blog/2013/01/28/currency-conversion-in-tabular-model-using-dax/.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com




    Tuesday, February 21, 2017 9:45 AM