Problem in Calculating the Subject and Comparison Period Revenue with multiple currencies

[Anonymous]

Hi all,

I am new to POWER BI.
I have a problem in calculating the Comparison_Period_Revenue.

My Source is SQL Server Analysis Services tabular live connection.

I have 4 tables

Stay Date

Calendar_Date

Compare Date
Compare_Date

Stay
Calendar_Date_Copy   Rooms       Revenue     Hotel_id
1/1/2018                           5                100             1
1/2/2018                           3                 60              1
1/1/2019                           4                120             1          
1/2/2019                           2                100             1
1/2/2019                           9                 150            2

Conversion Currency
Calendar_Date       Currency_Code              Exchange_Rate_Amount       Hotel_id
1/1/2018                      US Dollar                               0.12                             1
1/1/2018                      EURO                                    0.08                              1
1/2/2018                      US Dollar                               0.13                              1
1/1/2019                      Bhutan Currency                   1.50                              1
1/1/2019                      US Dollar                              1.02                              1
1/1/2019                      Bhutan Currency                    1.40                             2

The following are the relations between tables.

Stay Date --- Stay                                        Active
Stay Date --- Conversion Currency             Active
Compare Date --- Stay                                In Active
Compare Date --- Conversion Currency      In Active

Following are the filters
Calendar_Date
Compare_Date
Hotel_id
Currency_Code (Single Selection)

I want to display the results as following table.
---------------------------------------------------
Hotel_id Subject_Period_Revenue Comparison_Period_Revenue

I am able to calculate Subject Period Revenue.

Subject_Exchange_RATE = var rate=CALCULATE(FIRST NON BLANK('CONVERSION CURRENCY'[Exchange Rate  Amount],1),ALL SELECTED('STAY DATE'[Calendar Date]),USE RELATIONSHIP('STAY DATE'[Calendar Date],'CONVERSION CURRENCY'[CALENDAR_DATE]),FILTER('CONVERSION CURRENCY','CONVERSION CURRENCY'[Currency Code]=SELECTED VALUE('CONVERSION CURRENCY'[Currency Code])))
RETURN RATE
Subject Period Revenue = SUM(STAY[Stay Room Revenue])*'CONVERSION CURRENCY'[Subject_Exchange_RATE]

But i am unable to calculate the Comparison Period Revenue ( Revenue * Exchange Rate)

Any help would be appreciated.

[v-eachen-msft]

Hi @Anonymous ,

According to your description, you could create Comparison_Period_Revenue like the way to create Subject_Period_Revenue( replace 'STAY DATE'[Calendar Date] with 'Compare Date'[Compare_Date] ).

But i am unable to calculate the Comparison Period Revenue ( Revenue * Exchange Rate)

Is your result wrong or is your DAX not working? I am not very clear about it.

[Anonymous]

Thank you so much for the reply. 

Following is the DAX i created for the Comparison Period Revenue.

Compare Revenue = VAR MYRATE= CALCULATE(FIRSTNONBLANK('CONVERSION CURRENCY'[Exchange Rate Amount],1), ALLSELECTED('COMPARE DATE'[Calendar Date]),

FILTER('CONVERSION CURRENCY','CONVERSION CURRENCY'[Currency Code]=SELECTEDVALUE('CONVERSION CURRENCY'[Currency Code])))
RETURN (CALCULATE(SUM(STAY[Stay Room Revenue]),ALL('STAY DATE'[Calendar Date]),USERELATIONSHIP(STAY[Calendar Date Copy],'COMPARE DATE'[Calendar Date])))*MYRATE

But it is giving me the subject period exchange rate* Comparison Period Revenue.