Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

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 Cale...
Need Help
v-eachen-msft's avatar
v-eachen-msft
Icon for Community Support rankCommunity Support
7 years ago

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

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.