Forum Discussion

Glen_'s avatar
Glen_
Regular Visitor
4 years ago
Solved

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:
 

 

Currency sheet:

 

 

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

 

 

Thanks for any assistance

Currency
DAX
FX
  • Anonymous's avatar
    Anonymous
    4 years ago

    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:

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

     

    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]))

23 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello Glen_ 

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

    • Glen_'s avatar
      Glen_
      Regular Visitor
      4 years ago

      Hi Anonymous 

       

      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

       

       

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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]))

         

         

          

         

         

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

    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
    )