Forum Discussion

akim_no's avatar
akim_no
Icon for Helper III rankHelper III
1 year ago

Revenue in Different Currencies

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-...
Need Help
akim_no's avatar
akim_no
Icon for Helper III rankHelper III
1 year ago

Ashish_Mathur 

Rate 

Ratecurrency_codecurrency_idrate_monthrate_yearmonthYear
1.10USDUSDJanuary202301/2023
1.15USDUSDFebruary202302/2023
1.25USDUSDMarch202303/2023
1.00EUREUROctober202310/2023
1.00EUREURNovember202311/2023
1.00EUREURDecember202312/2023
130.50JPYJPYSeptember202309/2023
131.00JPYJPYOctober202310/2023
0.78GBPGBPSeptember202409/2024
0.80GBPGBPOctober202410/2024
1.05CHFCHFAugust202308/2023
1.06CHFCHFSeptember202309/2023
0.68AUDAUDJuly202407/2024
0.69AUDAUDAugust202408/2024
0.76CADCADJune202306/2023
0.77CADCADJuly202307/2023
6.89CNYCNYMay202305/2023
7.00CNYCNYJune202306/2023
74.25INRINRApril202404/2024
74.30INRINRMay202405/2024
1.36SGDSGDMarch202303/2023
1.37SGDSGDApril202304/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's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      akim_no
      Icon for Helper III rankHelper III
      1 year ago

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        1 year ago

        Hi,

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