Revenue in Different Currencies

[akim_no]

I need to analyze the revenue in local currency and in euros. For each month, we have the amount in local currency for the current year (Local Currency N) and for the previous year (Local Currency N-1). The amount for the previous year is calculated using the following formula:

Formula 1 - Local Currency N-1:
VAR PreviousYear = SELECTEDVALUE(Calendar[year]) - 1
VAR RPrevious = CALCULATE([Local Currency], Calendar[year] = PreviousYear)
RETURN RPrevious
```

I want to convert these amounts into euros using a specific exchange rate chosen by the user. The user selects the month and year in the 'MonthYear' column, formatted as 'MM-yyyy'. Based on this selection, the corresponding exchange rate is identified. This rate is stored in the 'rate' table, which is independent of the main semantic model.

Next, I generate the revenue in euros for the current year using the subsequent formula:

Formula 2 - Revenue € N :
VAR PeriodMonthYear = SELECTEDVALUE('rate'[MonthYear])
VAR RevenueInEuro = SUMX(
'Revenue',
DIVIDE(
'Revenue'[Local Currency N],
COALESCE(
LOOKUPVALUE('rate'[rate], 'rate'[currency_id], 'Revenue'[currency_id], 'rate'[MonthYear], PeriodMonthYear),
1
)
)
)
RETURN RevenueInEuro
```

When attempting to apply the same method to 'Currency Revenue N-1', although I can retrieve the appropriate values, the division results become incomprehensible.

Formula 3 - Revenue € N-1 (formula) :

VAR MonthYear = SELECTEDVALUE('rate'[MonthYear])
VAR PYYear = SELECTEDVALUE(Calendar[year]) - 1
VAR RevenueLocal = CALCULATE([Local Currency N], Calendar[year] = PYYear)
VAR CurrentCurrency = SELECTEDVALUE('Revenue'[currency_id])
VAR Rate = CALCULATE(
LOOKUPVALUE(
'rate'[rate],
'rate'[currency_id], CurrentCurrency,
'rate'[MonthYear], MonthYear
)
)
RETURN RevenueLocal & "/" & Rate
```

**Formula 4 - Revenue € N-1 (formula output) :
VAR MonthYear = SELECTEDVALUE('rate'[MonthYear])
VAR PYYear = SELECTEDVALUE(Calendar[year]) - 1
VAR RevenueLocal = CALCULATE([Local Currency], Calendar[year] = PYYear)
VAR RevenueEuro = SUMX(
'Invoice',
RevenueLocal /
COALESCE(
LOOKUPVALUE(
'rate'[rate],
'rate'[currency_id], 'Revenue'[currency_id],
'rate'[MonthYear], MonthYear
),
1
)
)
RETURN RevenueEuro

The table presents the results of the measures :

If anyone can help me to resolve this issue.

Thankx

[akim_no]

@Ashish_Mathur 

Rate 

Rate	currency_code	currency_id	rate_month	rate_year	monthYear
1.10	USD	USD	January	2023	01/2023
1.15	USD	USD	February	2023	02/2023
1.25	USD	USD	March	2023	03/2023
1.00	EUR	EUR	October	2023	10/2023
1.00	EUR	EUR	November	2023	11/2023
1.00	EUR	EUR	December	2023	12/2023
130.50	JPY	JPY	September	2023	09/2023
131.00	JPY	JPY	October	2023	10/2023
0.78	GBP	GBP	September	2024	09/2024
0.80	GBP	GBP	October	2024	10/2024
1.05	CHF	CHF	August	2023	08/2023
1.06	CHF	CHF	September	2023	09/2023
0.68	AUD	AUD	July	2024	07/2024
0.69	AUD	AUD	August	2024	08/2024
0.76	CAD	CAD	June	2023	06/2023
0.77	CAD	CAD	July	2023	07/2023
6.89	CNY	CNY	May	2023	05/2023
7.00	CNY	CNY	June	2023	06/2023
74.25	INR	INR	April	2024	04/2024
74.30	INR	INR	May	2024	05/2024
1.36	SGD	SGD	March	2023	03/2023
1.37	SGD	SGD	April	2023	04/2023

My Semantic model : 

What I'm looking for : 

Revenue Euro N-1 = 
VAR PYYear = SELECTEDVALUE(Revenue[Revenue year])-1
VAR PYRevenue = CALCULATE([Revenue N], (Revenue[Revenue year]) = PYYear)
VAR MonthYear = SELECTEDVALUE('Rate'[Rate]) 
VAR Revenue_Euro = SUMX(
    'Revenue',
    DIVIDE(
        PYRevenue, 
        COALESCE(
            LOOKUPVALUE(
                'Rate'[Rate],
                'Rate'[currency_code], 'Revenue'[currency_id],
                'Rate'[monthYear], MonthYear
            ),
            1
        )
    )
)
RETURN
Revenue_Euro

[Ashish_Mathur]

Still not very clear.  If possible, could you put this data in an MS Excel file and show the Excel formulas that you would have written to solve this problem.  I will convert those Excel formulas to measures/calculated columns.

[akim_no]

I have uploaded a Power BI file with sample data at this link: https://github.com/akimno/power-bi.

1. Converting revenue to euros:

   • The revenue is recorded in different currencies.
   • The user selects a month and a year using the filter (
     'Rate'[monthYear]).
   • Once the month and year are selected, I retrieve the exchange rate(
     'Rate'[Rate])
     for that period.
   • I divide the revenue amount in the local currency by the exchange rate to obtain the revenue in euros ('Revenue'[Revenue Euro N]).

2. Comparison with the previous year:

   • I want to calculate the revenue in euros for the previous year in the same way ('Revenue'[Revenue Euro N-1]).
   • Then, I want to display the comparison between the two (for example, as a difference).

The problem: I’m unable to correctly display the value of the previous year’s revenue.

[Ashish_Mathur]

Hi,

Please also share the download link of 3 source Excel files.

[akim_no]

Hi @Ashish_Mathur 

Done 

[Ashish_Mathur]

See if this helps.

[akim_no]

Hello @Ashish_Mathur 
In my table, I have different currencies for local revenues. Will the "selected rate" automatically pick the corresponding rate for each currency to perform the conversion?

[Ashish_Mathur]

Please try yourself.

[akim_no]

@Ashish_Mathur 

Revenue Table : 

Revenue_id	Revenue_id	Revenue_line_id	Revenue line number	Revenue number	Revenue product name	Invoiced quantity	Quantity	Local Revenue	Discount rate
R001	R001	RL001	1	45783	Widget	15	15	8500	5%
R002	R002	RL002	2	45874	Super Widget	20	20	7800	3%
R003	R003	RL003	3	45990	Mega Widget	10	10	10100	10%
R004	R004	RL004	4	45001	Economy Widget	30	30	1220	8%
R005	R005	RL005	5	44675	Premium Widget	12	12	5350	1%
R006	R006	RL006	6	44232	Widget Pro	25	25	4387	4%
R007	R007	RL007	7	43890	Widget Lite	40	40	3456	2%
R008	R008	RL008	8	43558	Widget Ultra	50	50	2369	5%
R009	R009	RL009	9	43100	Widget Standard	100	100	1575	15%
R010	R010	RL010	10	42843	Compact Widget	35	35	1962	7%
R011	R011	RL011	11	46002	Advanced Widget	18	18	8600	6%
R012	R012	RL012	12	46123	Basic Widget	22	22	7900	4%
R013	R013	RL013	13	46234	Ultra Widget	8	8	10200	11%
R014	R014	RL014	14	46345	Mini Widget	28	28	1240	9%
R015	R015	RL015	15	46456	Giant Widget	15	15	5400	2%
R016	R016	RL016	16	46567	Widget Extra	30	30	4300	5%
R017	R017	RL017	17	46678	Widget Special	45	45	3400	3%
R018	R018	RL018	18	46789	Widget Plus	60	60	2350	6%
R019	R019	RL019	19	46890	Widget Supreme	95	95	1600	14%
R020	R020	RL020	20	47001	Widget Micro	38	38	1950	8%

Revenue details Table : 

Revenue_id	Revenue_id	Revenue_line_id	Revenue line number	Revenue number	Revenue product name	Invoiced quantity	Quantity	Local Revenue	Discount rate
R001	R001	RL001	1	45783	Widget	15	15	8500	5%
R002	R002	RL002	2	45874	Super Widget	20	20	7800	3%
R003	R003	RL003	3	45990	Mega Widget	10	10	10100	10%
R004	R004	RL004	4	45001	Economy Widget	30	30	1220	8%
R005	R005	RL005	5	44675	Premium Widget	12	12	5350	1%
R006	R006	RL006	6	44232	Widget Pro	25	25	4387	4%
R007	R007	RL007	7	43890	Widget Lite	40	40	3456	2%
R008	R008	RL008	8	43558	Widget Ultra	50	50	2369	5%
R009	R009	RL009	9	43100	Widget Standard	100	100	1575	15%
R010	R010	RL010	10	42843	Compact Widget	35	35	1962	7%
R011	R011	RL011	11	46002	Advanced Widget	18	18	8600	6%
R012	R012	RL012	12	46123	Basic Widget	22	22	7900	4%
R013	R013	RL013	13	46234	Ultra Widget	8	8	10200	11%
R014	R014	RL014	14	46345	Mini Widget	28	28	1240	9%
R015	R015	RL015	15	46456	Giant Widget	15	15	5400	2%
R016	R016	RL016	16	46567	Widget Extra	30	30	4300	5%
R017	R017	RL017	17	46678	Widget Special	45	45	3400	3%
R018	R018	RL018	18	46789	Widget Plus	60	60	2350	6%
R019	R019	RL019	19	46890	Widget Supreme	95	95	1600	14%
R020	R020	RL020	20	47001	Widget Micro	38	38	1950	8%

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result very clearly.  Share data in a format that can be pasted in an MS Excel file.

[rajendraongole1]

Hi @akim_no  - The formulas and table you've provided indicate that you're trying to correctly calculate revenue in euros for both the current year and the previous year using exchange rates selected by the user. However, based on your issue, it seems that there might be a problem with how the calculations are applied to the previous year's data.

Steps to Debug the Issue
Formula for Local Currency N-1:

Your Formula 1 seems correct for retrieving the previous year's local currency values. Ensure that the Calendar[year] column is properly populated and connected to your data model.
Revenue € N:

Formula 2 seems to correctly divide the current year's local currency by the exchange rate using LOOKUPVALUE. Ensure that:
The rate table is properly formatted.
currency_id and MonthYear values match correctly between the rate and Revenue tables.
Revenue € N-1:

The issue lies in applying the same logic to the previous year's data. In Formula 3, you're concatenating RevenueLocal and Rate for debugging. If the division in Formula 4 fails or gives incorrect results, here are some checks:
Ensure that RevenueLocal is correctly calculated for the previous year. Debug by isolating this variable's output.
Check whether the LOOKUPVALUE in Rate is returning the expected value. If not, confirm that MonthYear and currency_id are consistent in the rate table.
Exchange Rate Application for Previous Year:

When calculating Revenue € N-1, you need to ensure that the MonthYear column reflects the correct year. It might be necessary to adjust it to MonthYear for the previous year:
DAX
Copy code
VAR AdjustedMonthYear = FORMAT(DATE(PYYear, MONTH(Calendar[date]), 1), "MM-yyyy")
Test the Division Logic:

If Formula 4 outputs incomprehensible results, debug by testing each step:
Calculate and return just RevenueLocal.
Calculate and return just the Rate.
Divide the two in isolation to confirm their compatibility.

VAR MonthYear = SELECTEDVALUE('rate'[MonthYear])
VAR PYYear = SELECTEDVALUE(Calendar[year]) - 1
VAR AdjustedMonthYear = FORMAT(DATE(PYYear, MONTH(Calendar[date]), 1), "MM-yyyy")
VAR RevenueLocal = CALCULATE([Local Currency], Calendar[year] = PYYear)
VAR Rate = LOOKUPVALUE(
'rate'[rate],
'rate'[currency_id], 'Revenue'[currency_id],
'rate'[MonthYear], AdjustedMonthYear
)
VAR RevenueEuro = DIVIDE(RevenueLocal, COALESCE(Rate, 1))
RETURN RevenueEuro

Let me know if further adjustments are needed!