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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Glen_
Regular Visitor

DAX currency translation

Hi,

 

I would be really greatful is someone could assist.  I'm very new to power BI and after searching a lot I'm still struggling for a solution here that suits my setup.

 

My data is all in one currency & I want to convert it into different versions of dollar rates.  The DAX query i pulled together worked okish when I had the same rate for each period (I dont have dates in the data, just numbered financial periods 1 to 12).

 

However some of my currency scenarios have a different rate per period and they pull in blank, so I need help changing this query to take into account the rates at different periods.

 

The second issue is in my value column I'm converting there is also volume that I do no want to convert and keep at the same value, this pulls from my P&L column in the data sheet

 

Current DAX formula:

 

FX adjusted value =
var value_ = CALCULATE(DATASHEETS[Sum of Value])
var ExchangeRate_ = selectedvalue('Currency'[Currency rate])
return
value_ * ExchangeRate_
 
Data sheet:
 
Glen__0-1666604297294.png

 

Currency sheet:

 

Glen__1-1666604393016.png

 

Also including relationships if this helps - the Period reference is what links them

 

Glen__2-1666604462347.png

 

Thanks for any assistance

1 ACCEPTED SOLUTION
eliasayy
Impactful Individual
Impactful Individual

ok i duplicated the values so i can have a similar scenario:
im still not sure if i understood correctly but here is my new table i sampled randome data numbers:

Screenshot 2022-10-24 153201.png

as you see  i have multiple gross profit on period 9 or 12 or whatever:

Screenshot 2022-10-24 153145.png

sum of values = CALCULATE(SUM(Daatasheet[Value]),ALL(Daatasheet[Value]))


now use the same formula but use:

Correct amount = IF(FIRSTNONBLANK(Daatasheet[P&L],1)<>"Volume",SUMX('Currency','Currency'[Currency rate] *[valueis]),SUM(Daatasheet[Value]))

View solution in original post

23 REPLIES 23
tamerj1
Super User
Super User

Hi @Glen_ 
Kindly refer to attached file with the proposed solution. I hope this is what you're looking for.

1.png

Amount = 
VAR CurrentPL = SELECTEDVALUE ( 'P L Dimension'[P&L] )
VAR VolumeAmount = SUM ( DATASHEETS[Value] )
VAR NormalAmount = 
    SUMX ( 
        'Currency',
        VAR CurrentRate = 'Currency'[Currency rate]
        VAR CurrentAmount = 
            CALCULATE ( 
                SUM ( DATASHEETS[Value] ), 
                CROSSFILTER ( 'Currency'[Period], 'Time Dimension'[Period], BOTH ) 
            )
        RETURN
            CurrentAmount * CurrentRate
    )
RETURN
    IF (
        CurrentPL = "Volume",
        VolumeAmount,
        NormalAmount
    )
eliasayy
Impactful Individual
Impactful Individual

Hello @Glen_ 

Can you please provide some data sample and an example where it went wrong 

Hi @eliasayy 

 

No problem, I've added a sample of the data below

 

Also - screenshot of the results (from a more complete set):

 

Volume figures are having fx changes applied- need these to stay the same as they are not impacted by currency rates, GBP vols are correct as a rate of 1 is being applied.

 

Only 3 currencies are pulling in (the ones that have exactly the same rate each period).  "$ - PY" & "$ - F93" are not and they vary by period

 

Glen__0-1666607214599.png

 

DATASHEETS:

 

BUScenarioPERIODSKU CODECustomer P&LValue
BCS UKFinal Plan 2022125000001356DISCOUNTERSGross Profit3024.5126
BCS UKFinal Plan 2022125000001356DISCOUNTERSNet Sales4660
BCS UKFinal Plan 2022125000001356DISCOUNTERSGross Sales4660
BCS UKFinal Plan 2022125000001356DISCOUNTERSVolume2000
BCS UKFinal Plan 2022115000001356DISCOUNTERSGross Profit3024.5126
BCS UKFinal Plan 2022115000001356DISCOUNTERSNet Sales4660
BCS UKFinal Plan 2022115000001356DISCOUNTERSGross Sales4660
BCS UKFinal Plan 2022115000001356DISCOUNTERSVolume2000
BCS UKFinal Plan 2022105000001356DISCOUNTERSGross Profit3024.5126
BCS UKFinal Plan 2022105000001356DISCOUNTERSNet Sales4660
BCS UKFinal Plan 2022105000001356DISCOUNTERSGross Sales4660
BCS UKFinal Plan 2022105000001356DISCOUNTERSVolume2000
BCS UKFinal Plan 202295000001356DISCOUNTERSGross Profit3024.5126
BCS UKFinal Plan 202295000001356DISCOUNTERSNet Sales4660
BCS UKFinal Plan 202295000001356DISCOUNTERSGross Sales4660
BCS UKFinal Plan 202295000001356DISCOUNTERSVolume2000
BCS UKFinal Plan 202285000001356DISCOUNTERSGross Profit3024.5126

 

Currency:

 

PeriodCurrencyCurrency rate
1GBP1
2GBP1
3GBP1
4GBP1
5GBP1
6GBP1
7GBP1
8GBP1
9GBP1
10GBP1
11GBP1
12GBP1
1$ - P221.38889
2$ - P221.38889
3$ - P221.38889
4$ - P221.38889
5$ - P221.38889
6$ - P221.38889
7$ - P221.38889
8$ - P221.38889
9$ - P221.38889
10$ - P221.38889
11$ - P221.38889
12$ - P221.38889
1$ - F931.35583
2$ - F931.34944
3$ - F931.3132
4$ - F931.28341
5$ - F931.24614
6$ - F931.22784
7$ - F931.20292
8$ - F931.18881
9$ - F931.13036
10$ - F931.137829991
11$ - F931.137829991
12$ - F931.137829991
1$ - P231.137829991
2$ - P231.137829991
3$ - P231.137829991
4$ - P231.137829991
5$ - P231.137829991
6$ - P231.137829991
7$ - P231.137829991
8$ - P231.137829991
9$ - P231.137829991
10$ - P231.137829991
11$ - P231.137829991
12$ - P231.137829991
1$ - PY1.36521
2$ - PY1.3929
3$ - PY1.38469
4$ - PY1.38592
5$ - PY1.41457
6$ - PY1.39748
7$ - PY1.38329
8$ - PY1.37772
9$ - PY1.37094
10$ - PY1.36657
11$ - PY1.33914
12$ - PY1.33169

 

Thanks again

eliasayy
Impactful Individual
Impactful Individual

hello thank you for providing me the details
im not sure i understood  you correctly but is this the result you want?

 

 

Correct amount = IF(SELECTEDVALUE(Daatasheet[P&L]) <>"Volume",SUMX('Currency','Currency'[Currency rate] * CALCULATE(VALUES(Daatasheet[Value]))),SUM(Daatasheet[Value]))

 

 

Screenshot 2022-10-24 140551.png

Screenshot 2022-10-24 140612.png

  

I get this error when I try to recreate using that line?

 

Glen__0-1666609483960.png

 

Looking at your screenshot, the volumes (from P&L field in datasheet) are changing - these would need to stay the same and not multiplied by fx rates?

 

Glen__1-1666609720430.png

 

Thanks again

eliasayy
Impactful Individual
Impactful Individual

you can also use this if you want to have multiple condition :

Correct amount = IF(AND( SELECTEDVALUE(Daatasheet[P&L])<>"Volume",SELECTEDVALUE(Daatasheet[P&L])<>"Gross Profit"),SUMX('Currency','Currency'[Currency rate] * CALCULATE(SELECTEDVALUE(Daatasheet[Value]))),SUM(Daatasheet[Value]))
eliasayy
Impactful Individual
Impactful Individual

can you show me what you have in the table? and i reread the issue and edited my post please see if this is the result you want

 

Hi   

I've updated to match yours, but still get this message. No error message when saving the formula - but no luck calculating the table

 

Could this be because Period number is used multiple times in the datasheet, along with the currency sheet?

 

 fxv2 = if(SELECTEDVALUE(DATASHEETS[P&L]) <>"Volume",SUMX('Currency','Currency'[Currency rate] * CALCULATE(VALUES(DATASHEETS[Value]))),SUM(DATASHEETS[Value])) 

 

Glen__0-1666610306882.png

 

 

@eliasayy

eliasayy
Impactful Individual
Impactful Individual

try selectedvalue instead of values
if this still doesnt work replace it with firstnonblank(datasheet[value],0)

so uou get something like 

Correct amount = IF(FIRSTNONBLANK(Daatasheet[P&L],0)<>"Volume",SUMX('Currency','Currency'[Currency rate] * CALCULATE(FIRSTNONBLANK(Daatasheet[Value],0))),SUM(Daatasheet[Value]))

with selected value - volumes now pull in and are all the same which is good, but none of the other P&L lines are coming in:

 

fxv2 = if(SELECTEDVALUE(DATASHEETS[P&L]) <>"Volume",SUMX('Currency','Currency'[Currency rate] * CALCULATE(SELECTEDVALUE(DATASHEETS[Value]))),SUM(DATASHEETS[Value]))
 
Glen__0-1666611189509.png

 

Thanks again

eliasayy
Impactful Individual
Impactful Individual

if this still doesnt work do you think you can share your powerbi file?
if no provide a full page of all filter and slicers you might be using

eliasayy
Impactful Individual
Impactful Individual

ok try

Correct amount = IF(FIRSTNONBLANK(Daatasheet[P&L],0)<>"Volume",SUMX('Currency','Currency'[Currency rate] * CALCULATE(FIRSTNONBLANK(Daatasheet[Value],0))),SUM(Daatasheet[Value]))

I get numbers now in for the other P&L lines - but much lower than they should be:

 

This was before (where i could only pull in where the rate was the same for every period.  You can see positive figures & much higher values:

 

Glen__0-1666611831148.png

 

with this solution, the numbers are negative and very low.  The volume is correct though

 

Glen__1-1666611916232.png

 

Revisiting the model - its linked to the time dimension, as I think DATASHEETS have multiple different results for each period unlike Time Dimension:  Sample below, could this help?

 

Glen__2-1666612083510.png

 

Time Dimension

 

PeriodPeriod2QuarterFYHalf
1P01Q1FYH1
2P02Q1FYH1
3P03Q1FYH1
4P04Q2FYH1
5P05Q2FYH1
6P06Q2FYH1
7P07Q3FYH2
8P08Q3FYH2
9P09Q3FYH2
10P10Q4FYH2
11P11Q4FYH2
12P12Q4FYH2

 

Thanks

eliasayy
Impactful Individual
Impactful Individual

does your values for example gross profit has multiple values in the same period?
say 

PeriodP&LValue
1gross profit-10,000
1gross profit-4,000
1gross profit20,000

Thats correct it does, the data is for multiple customers & they sometimes take the same products so there will be many thousand rows with say period 1 and gross profit and a different value figure

eliasayy
Impactful Individual
Impactful Individual

ok this is why its giving you the numebrs the formula adds all period 1 gross profit so can you please tell me exactly how you want to filter it out?

Sure, so I'm looking to be able to slice between the different currencies.

 

If no other slices / filters are on I would be looking for everything to total Period 1 to 12 applying the individual rate for each period to every line in the datasheet (except volume), and pull the results depending on which currency rate is selected in the filters.

 

Thanks again

 

 

eliasayy
Impactful Individual
Impactful Individual

ok i duplicated the values so i can have a similar scenario:
im still not sure if i understood correctly but here is my new table i sampled randome data numbers:

Screenshot 2022-10-24 153201.png

as you see  i have multiple gross profit on period 9 or 12 or whatever:

Screenshot 2022-10-24 153145.png

sum of values = CALCULATE(SUM(Daatasheet[Value]),ALL(Daatasheet[Value]))


now use the same formula but use:

Correct amount = IF(FIRSTNONBLANK(Daatasheet[P&L],1)<>"Volume",SUMX('Currency','Currency'[Currency rate] *[valueis]),SUM(Daatasheet[Value]))

Thats done it, thank you so much for taking all this time to help me & sorry if I didn't explain it the best to start with, again I'm very new to it all.

 

Have a great day

eliasayy
Impactful Individual
Impactful Individual

im glad i could help my friend its okay we all were once newbies

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors