Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
khaeshr46
Frequent Visitor

Constant Currency dynamic calculation help needed

Hi,

I need to create a dynamic Constant exchange calculation for 3 prior years using max rates of the currently selected Quarter Year exchange rates. I have all my sales in Local Currency

 

khaeshr46_0-1697177379642.png

 

khaeshr46_3-1697177799453.png

 

I need to convert the PY Sales LC x 9/30/15 rates for my PY Sales USD(CC)

 

The mesaure below is giving me the correct CC amount Im looking for in the current slicer selection. How can I modify this to apply for Prior Year Sales LC

 

Sales USD (CC) =
var ccDate =
Constant Currency Date = CALCULATE(
        MAX('FX Rate'[Date]),
        ALLSELECTED('Calendar_Lookup'[Date])
)
VAR vSalesWithConstantRate =
SUMMARIZE(
    Sales,
    'Currency'[Currency],
    "_LC Amount",
    [Sales LC],
    "_Constant Rate",
    CALCULATE(
        MAX('FX Rate'[Fx Rate to USD]),
        FILTER(
            ALL('FX Rate'),
            'FX Rate'[Date] = ccDate
            && 'FX Rate'[Currency] = SELECTEDVALUE('Currency'[Currency])
        )
    )
)
RETURN
SUMX(vSalesWithConstantRate, [_LC Amount]/[_Constant Rate])
1 ACCEPTED SOLUTION

 

PY Sales USD = 
var a = summarize('Fact',[Currency],[Date])
var b = ADDCOLUMNS(a,"fx",CALCULATE(max('Currency'[Fx Rate to USD]),TREATAS({[Date]},'Currency'[Date]),treatas({[Currency]},'Currency'[Currency])))
var c = ADDCOLUMNS(b,"p",[PY Sales LC])
return sumx(c,[fx]*[p])

 

View solution in original post

8 REPLIES 8
lbendlin
Super User
Super User

In your situation it is easier (and faster) to use TREATAS to project the filter across the sales and FX tables.

 

If you like more assistance - please share sample data.

Hi,

Below is some sample data and a new screenshot below of what is required.

 

PY Sales USD measure is calculating correctly but any suggestion to make the measure perform faster would be appriciated.

 

PY Sales USD Latest Month Rate is not working.

Also, if I would like to create a PY Full Year Sales USD as well using the selected quarter's exchange rates.

 

Currency Table

DateCurrencyFx Rate to USD
07/31/13USD1.00000
07/31/13CAD1.02870
08/31/13EUR1.32350
08/31/13USD1.00000
08/31/13CAD1.05530
09/30/13CAD1.02850
09/30/13EUR1.35050
09/30/13GBP1.61530
09/30/13USD1.00000

 

Fact Table

DateLC AmountCurrencyProductID
07/31/12  96,444,348.00USD113
07/31/12     272,570,350.80CAD122
07/31/12        41,111,070.00USD129
08/31/12     153,148,122.70CAD122
08/31/12        54,166,612.50USD129
09/30/12     352,368,933.70CAD122
09/30/12     290,977,531.20USD113
09/30/12        41,388,847.50USD129
07/31/13        41,772,180.45USD129
07/31/13     466,478,260.70CAD122
08/31/13        65,741,175.92EUR157
08/31/13        66,666,600.00USD113
08/31/13        25,972,196.25USD129
08/31/13     378,041,253.10CAD122
09/30/13        14,922,207.30USD129
09/30/13     281,268,319.20CAD122
09/30/13        58,975,793.59EUR157
09/30/13        94,719,152.48GBP176
09/30/13        66,666,600.00USD113

 

khaeshr46_0-1697310828227.png

Measures

PY Sales USD =
VAR vSalesWithFXRate =
    ADDCOLUMNS (
        SUMMARIZE (
            Sales,
            'Calendar_Lookup'[Date],
            'Currency'[Currency]
        ),
        "_LC Amount", [PY Sales LC],
        "_FX Rate to USD", CALCULATE (
            SELECTEDVALUE ( 'FX Rate'[Fx Rate to USD])
        )
    )

RETURN
    SUMX (
        vSalesWithFXRate,
        [_LC Amount] / [_FX Rate to USD]
    )
PY Sales USD Latest Month Rate =
var ccDate = CALCULATE(
        MAX('FX Rate'[Date]),
        ALLSELECTED('Calendar_Lookup'[Date])
)
VAR vSalesWithConstantRate =
SUMMARIZE(
    Sales,
    'Currency'[Currency],
    "_LC Amount",
    [PY Sales LC],
    "_Constant Rate",
    CALCULATE(
        MAX('FX Rate'[Fx Rate to USD]),
        FILTER(
            ALL('FX Rate'),
            'FX Rate'[Date] = ccDate
            && 'FX Rate'[Currency] = SELECTEDVALUE('Currency'[Currency])
        )
    )
)
RETURN
SUMX(vSalesWithConstantRate, [_LC Amount]/[_Constant Rate])
 

 

Here is what I have so far. Please have a look at my approach. I guess I don't understand this part:

 

using max rates of the currently selected Quarter 

 

The measures can be adjusted to the last available FX, for example.

Hi,
The TREATAS works well at row context. But the total was incorrect so i wrapped it in a SUMX SUMMARIZE for Sales USD measure and its giving me the right total. (Sales USD Correct Total)
 
The PY Sales USD however does not work with SUMX SUMMARIZE. (PY Sales USD Correct Total)
 
Sorry I was a bit unclear on the "using max rates of the currently selected Quarter" requirement. What I am looking to do is use the max date's FX rates of a selected Quarter. Please see the screenshot below. 
 
 
Sales USD Correct Total = SUMX(SUMMARIZE('Fact','Fact'[Date],'Fact'[Currency],"A",sum('Fact'[LC Amount])*CALCULATE(max('Currency'[Fx Rate to USD]),TREATAS(values('Fact'[Date]),'Currency'[Date]),treatas(values('Fact'[Currency]),'Currency'[Currency]))),[A])
 
PY Sales USD Correct Total = SUMX(SUMMARIZE('Fact','Fact'[Date],'Fact'[Currency],"A",[PY Sales LC]*CALCULATE(max('Currency'[Fx Rate to USD]),TREATAS(values('Fact'[Date]),'Currency'[Date]),treatas(values('Fact'[Currency]),'Currency'[Currency]))),[A])
 
khaeshr46_1-1697381444146.png

 


 

 
 

 

PY Sales USD = 
var a = summarize('Fact',[Currency],[Date])
var b = ADDCOLUMNS(a,"fx",CALCULATE(max('Currency'[Fx Rate to USD]),TREATAS({[Date]},'Currency'[Date]),treatas({[Currency]},'Currency'[Currency])))
var c = ADDCOLUMNS(b,"p",[PY Sales LC])
return sumx(c,[fx]*[p])

 

Hi,

Im still struggling to create a new measure that uses the the MAX date's rate depending what the user has selected in the slicer.

 

Below is my expected output.

 

DateCurrencySales LCSales USDSales USD Correct TotalSales USD with Latest Rates
7/31/2012 0:00CAD$272,570,350.80   
7/31/2012 0:00USD$137,555,418.00   
8/31/2012 0:00CAD$153,148,122.70   
8/31/2012 0:00USD$54,166,612.50   
9/30/2012 0:00CAD$352,368,933.70   
9/30/2012 0:00USD$332,366,378.70   
7/31/2013 0:00CAD$466,478,260.70$479,866,186.78$479,866,186.78$479,772,891.13
7/31/2013 0:00USD$41,772,180.45$41,772,180.45$41,772,180.45$41,772,180.45
8/31/2013 0:00CAD$378,041,253.10$398,946,934.40$398,946,934.40$388,815,428.81
8/31/2013 0:00EUR$65,741,175.92$87,008,446.33$87,008,446.33$88,783,458.08
8/31/2013 0:00USD$92,638,796.25$92,638,796.25$92,638,796.25$92,638,796.25
9/30/2013 0:00CAD$281,268,319.20$289,284,466.30$289,284,466.30$289,284,466.30
9/30/2013 0:00EUR$58,975,793.59$79,646,809.24$79,646,809.24$79,646,809.24
9/30/2013 0:00GBP$94,719,152.48$152,999,847.00$152,999,847.00$152,999,847.00
9/30/2013 0:00USD$81,588,807.30$81,588,807.30$81,588,807.30$81,588,807.30

 

khaeshr46_0-1697606785873.png

 

I don't see a difference to the other columns.

Thanks alot!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors