Calculate revenue based on exchange rate of RMB

[v-lili6-msft]

Scenario:  

Suppose I would like to calculate the revenue of each Country/Region based on the exchange rate of RMB(China) in different effective period. How will I achieve this requirement?  

Sample Data:  

Method1: Create a measure 

1. Find the matched rate of the specific period for each country. 
2. Calculate the product of rate and revenue. 
3. Use SUMX() function to sum the product. 

DAX Operation:  

l  Product =  

VAR _rate = 

    CALCULATE ( 

        MAX ( 'Rates'[Rate] ), 

        FILTER ( 

            'Rates', 

            'Rates'[Effective End Date] >= MAX ( 'Revenue'[Date] ) 

                && 'Rates'[Effective Start Date] <= MAX ( 'Revenue'[Date] ) 

                && 'Rates'[Country/Region] = MAX ( 'Revenue'[Country/Region] ) 

        ) 

    ) 

RETURN 

    _rate * MAX ( 'Revenue'[Revenue] ) 

l  Result Measure = 

SUMX ( 'Revenue', [Product] ) 

Output: 

 

Method2: Add a column 

The same as method 1. 

DAX Operation:  

Result Column =  

VAR _coun = [Country/Region] 

VAR _date = [Date] 

VAR _revenue = [Revenue] 

RETURN 

    SUMX ( 

        FILTER ( 

            ALL ( 'Rates' ), 

            [Effective End Date] >= _date 

                && [Effective Start Date] <= _date 

                && [Country/Region] = _coun 

        ), 

        [Rate] * _revenue 

) 

Output: 

Method3: Create a calculated table 

1. Combine these two tables using CROSSJOIN() function. I will use SELECTCOLUMNS() function to rename one column as the “Country/Region” columns are the same in both tables. 
2. Filter out the data where 

          1)  Date is not between start date and end date. 

          2)  Country/Region in the Rate table is not the same as [C/R] in the Revenue table. 

1. Add a new column:[Revenue]*[Rate]. 

DAX Operation:  

Crossjoin Table = 

ADDCOLUMNS ( 

    FILTER ( 

        CROSSJOIN ( 

            'Rates', 

            SELECTCOLUMNS ( 

                'Revenue', 

                "C/R", [Country/Region], 

                "Date", [Date], 

                "Revenue", [Revenue] 

            ) 

        ), 

        [Country/Region] = [C/R] 

            && [Date] >= [Effective Start Date] 

            && [Date] <= [Effective End Date] 

    ), 

    "Result", [Revenue] * [Rate] 

) 

Output: 

 

Method4: In Power Query 

1. Merge these two tables according to Country/Region and expand the items. 
2. Filter out dates that are not between the Start date and End date. 
3. Add a custom column= [Revenue]*[Rate]. 

  

M Operation in Advanced Editor:  

let 

    Source = Table.NestedJoin(Revenue, {"Country/Region"}, Rates, {"Country/Region"}, "Rates", JoinKind.LeftOuter), 

    #"Expanded Rates" = Table.ExpandTableColumn(Source, "Rates", {"Effective Start Date", "Effective End Date", "Rate"}, {"Rates.Effective Start Date", "Rates.Effective End Date", "Rates.Rate"}), 

    #"Filter Tables"=Table.SelectRows(#"Expanded Rates",each [Date] >= [Rates.Effective Start Date] and  [Date]<= [Rates.Effective End Date] ), 

    #"Renamed Columns" = Table.RenameColumns(#"Filter Tables",{{"Rates.Effective Start Date", "Effective Start Date"}, {"Rates.Effective End Date", "Effective End Date"}, {"Rates.Rate", "Rate"}}), 

    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Result", each [Revenue]*[Rate]), 

    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Result", type number}}) 

in 

#"Changed Type" 

Output: 

 

Please check the attached file for details.  

I hope this article can help you with the similar question. 

Author: Eyelyn Qin 

Reviewer:   Icey Zhang & Lin Li 

Calculate revenue based on exchange rate.pbix