Currency Exchange rate today only

[pswprimera]

Hello,

I have what seems to be a tricky task using DAX in Power BI.

I have 3 columns

Date

Amount

exchange rate

I need todays exchange rate only to calculate historical revenue. I dont want to use tomorrows exchange rate to calculate tomorrows revenue.

Amount / exchange rate (today) = Revenue

The 4th Column or Measure Revenue must only use todays exchange rate.

Please help I've tried everything and it seems over complicated.

[Phil_Seamark]

Have a crack at this 

New Column = 'Exchange'[Amount] * 
        CALCULATE(
            MAX('Exchange'[Exchange Rate]),
            FILTER(
                'Exchange',
                'Exchange'[DATE]=TODAY())
                )

[pswprimera]

sorry this didn't work the figures are way out.

Todays date should always =  today for the exchange rate only.

todays date yesterday, the day before that, etc,etc and all historical data for the amount should be divided by todays exchange rate.

amount / (exchange rate today)

[DoubleJ]

Hi

I don't completely understand your requirement. First off, how do you know tomorrows exchange rate?

Further questions
- Is there always one record per day? 
- Is there a record every day?

- What do you refer as todays exchange rate?
- Do you want to use the same (most recent) exchange rate for all records? That would mean that the values change every time the exchange rate changes.

Please clarify your requirements

JJ

[pswprimera]

Hi Thanks for the quick reply

There are 4 amounts each day in different currencies

there is an amount a currency rate and a date for each value each day.

exchange rate would be currency converted to euro

yes I want to use the same most recent exchange rate for all records. yes the value would change every time the exchange rate changes.

[v-shex-msft]

Hi @pswprimera,

According to your description, you want to calculate the yesterday of current rows' exchange rate with current value, right?

If this is a case, you can try to use below measure:

Current Value=
var currDate= MAX(Table[Date])
Return
MAX(Table[Amount]) / LOOKUPVALUE(Table[Exchange Rate],Table[Date],currDate-1)

Regards,

XIaoxin Sheng

[pswprimera]

Thanks for the reply:

No I always want todays date for the exchange rate.

the amount column has all its historical values

the date column has all its historical values

exchange rate should just be today updating to a new value each day. 

revenue = amount / exchange rate of today

for example I want to see these results

date            amount ex rate reveneue

01/03/2017 100       1          100

28/02/2017 200       1.8       200

27/02/2017 300      2.0        300

so only todays ex rate is used for all revenue calculations.

[v-shex-msft]

Hi @pswprimera,

You can take a look at below formula:

Measure:

Current Value=
var currDate= Today()
Return
MAX(Table[Amount]) / LOOKUPVALUE(Table[Exchange Rate],Table[Date],currDate)

Regards,

Xiaoxin Sheng

[pswprimera]

Looks good but

A table of multiple values was supplied where a single value was expected. is the result

[v-shex-msft]

Hi @pswprimera,

>>A table of multiple values was supplied where a single value was expected. is the result

It seems like your table contains the duplicate data, I modified the formula to choose the max one:

Current Value=
var currDate= TODAY()
Return
MAX(Table[Amount]) / MAXX(FILTER(ALL('Exchange'),'Exchange'[DATE]=currDate),'Exchange'[Exchange Rate])

Regards,

Xiaoxin Sheng

[pswprimera]

This is close but not quite there

Current Value=

var currDate= TODAY()

Return

MAX(Table[Amount]) / MAXX(FILTER(ALL('Exchange'),'Exchange'[DATE]=currDate),'Exchange'[Exchange Rate])

If I run this formula for the last two dates removing MAX(Table[Amount]) / I get the following results

Current Value   creation_time        currency

112.63          2017-03-02 00:00:00  DKK

112.63          2017-03-02 00:00:00  EUR

112.63          2017-03-02 00:00:00  ISK

112.63          2017-03-02 00:00:00  NOK

112.63          2017-03-02 00:00:00  SEK

112.63          2017-03-03 00:00:00  DKK

112.63          2017-03-03 00:00:00  EUR

112.63          2017-03-03 00:00:00  ISK

112.63          2017-03-03 00:00:00  NOK

112.63          2017-03-03 00:00:00  SEK

Is there a way to get the current value for each currency rather than just the highest of all? 

[pswprimera]

If I run this formula for the last two dates removing MAX(Table[Amount]) / I get the following results

Current Value   creation_time        currency

112.63          2017-03-02 00:00:00  DKK

112.63          2017-03-02 00:00:00  EUR

112.63          2017-03-02 00:00:00  ISK

112.63          2017-03-02 00:00:00  NOK

112.63          2017-03-02 00:00:00  SEK

112.63          2017-03-03 00:00:00  DKK

112.63          2017-03-03 00:00:00  EUR

112.63          2017-03-03 00:00:00  ISK

112.63          2017-03-03 00:00:00  NOK

112.63          2017-03-03 00:00:00  SEK

What I should see is each exchange rate for today and not just the highest from all today. Is there a way of doing this then? It's close but not good.

[pswprimera]

Hi Using that calculation i should get the following

amount 4105304

ex rate today 7.43

revenue should = 4105304 / 7.43

revenue should = 550,530

but the answer from the calculation is 51,321,319

[v-shex-msft]

Hi @pswprimera,

Can you share some sample data? I test my measure without any issue.

 

Regards,

Xiaoxin Sheng

[pswprimera]

Raw Data atached. Please bare inmind the multiple currencies when making the calculation.

Date                                   amount            EX Rate      currency

2017-03-03 00:00:00          156056                              EUR

2017-03-03 00:00:00          25660472          7.43          DKK

2017-03-03 00:00:00          0                        8.88          NOK

2017-03-03 00:00:00          9161907            9.52          SEK

2017-03-03 00:00:00          117208758        112.63      ISK

2017-03-02 00:00:00          347589                               EUR

2017-03-02 00:00:00          101474445        7.43          DKK

2017-03-02 00:00:00          228000              8.86          NOK

2017-03-02 00:00:00          75174758          9.53          SEK

2017-03-02 00:00:00          231343048        113.37      ISK

2017-03-01 00:00:00          906845                              EUR

2017-03-01 00:00:00          106703616        7.43        DKK

2017-03-01 00:00:00          122000              8.87           NOK

2017-03-01 00:00:00          49957016          9.57         SEK

2017-03-01 00:00:00          260890723        113.95     ISK

[pswprimera]

Hi,

I noticed that the problem with my data is that the max(date) = 31/12/2017.

Is there a way round this?

[pswprimera]

From the below data I need to calculate revenue.

The simple version of the calculation would be

amount / exchange rate (always today) = revenue (this would be for all currencies)

so for today the calc would be 25660572 / 7.43 for DKK

9161907 / 9.52 for sek etc

and for yesterday 2017-03-02

101474445 / 7.43 for DKK

75174758 / 9.52 for SEK

Date                                   amount            EX Rate      currency

2017-03-03 00:00:00          156056                              EUR

2017-03-03 00:00:00          25660472          7.43          DKK

2017-03-03 00:00:00          0                        8.88          NOK

2017-03-03 00:00:00          9161907            9.52          SEK

2017-03-03 00:00:00          117208758        112.63      ISK

2017-03-02 00:00:00          347589                               EUR

2017-03-02 00:00:00          101474445        7.42          DKK

2017-03-02 00:00:00          228000              8.86          NOK

2017-03-02 00:00:00          75174758          9.53          SEK

2017-03-02 00:00:00          231343048        113.37      ISK

2017-03-01 00:00:00          906845                              EUR

2017-03-01 00:00:00          106703616        7.43        DKK

2017-03-01 00:00:00          122000              8.87           NOK

2017-03-01 00:00:00          49957016          9.57         SEK

2017-03-01 00:00:00          260890723        113.95     ISK

When the values change tomorrow as below for example the calculation will automatically use the exchange rate from that day.

2017-03-04 00:00:00          10660472          7.54          DKK

2017-03-04 00:00:00          77957016          9.62         SEK

so calculation would update automatically

for yesterday 2017-03-02

101474445 / 7.54 for DKK

75174758 / 9.62 for SEK

etc etc for all historical amounts.

Basically exchange rate should always use date = today as explained above.

[v-shex-msft]

Hi @pswprimera,

I modify the formula to add the condition to calculate the result of same rate: (measure and calculate column versions)

Measure:

Current Value = 
var currDate= TODAY()
var currRage= LASTNONBLANK('sample file'[Rate],[Rate])
Return
MAX('sample file'[Amount]) / MAXX(FILTER(ALL('sample file'),'sample file'[Date]=currDate&&'sample file'[Rate]=currRage),'sample file'[EX])

Calcualte column:

Current = 
var temp=MAXX(FILTER(ALL('sample file'),'sample file'[Date]=EARLIER('sample file'[Date])&&'sample file'[Rate]=EARLIER('sample file'[Rate])),'sample file'[EX])
return
'sample file'[Amount] / temp  

Regards,

Xiaoxin Sheng

[pswprimera]

Hi Xiaoxin

'sample file'[Rate])),'sample file'[EX])

What does EX refer to in the column and measure? Is this the same as rate? 

[v-shex-msft]

Hi @pswprimera,

>>Is this the same as rate? 

They has the same result. The var variables are same as earlier function, it used to get the value of current row content.

Regards,

Xiaoxin Sheng

[pswprimera]

Hi

When I use the measure and the calc column. The figures are still out:

creation_time   amount      ex rate     curr    CValue3        CVAL4

2017-03-06      5000                          EUR     NaN            Infinity

2017-03-06      2406490     7.43370  DKK     323727.08      38593.29

2017-03-06      2331200     9.53750  SEK     244424.63      9320.47

2017-03-06      0           113.22098    ISK       0                0

2017-03-05      475949                      EUR     NaN            Infinity

2017-03-05      146298549   7.43370 DKK    19680448.36    30445.40

2017-03-05      72000       8.93030     NOK   8062.43        Infinity

2017-03-05      63340552    9.53750  SEK     6641211.21     24477.58

2017-03-05      258822067   113.19871 ISK     2286440.00     Infinity

2017-03-04      212292                          EUR     NaN            Infinity

2017-03-04      71738594    7.43370  DKK     9650455.89     34481.07

2017-03-04      36000       8.93030     NOK     4031.21        Infinity

2017-03-04      45232137    9.53750  SEK     4742556.95     31820.07

2017-03-04      203061522  113.12386   ISK     1795037.12     Infinity

2017-03-03      660274                       EUR     NaN            Infinity

2017-03-03      76879336    7.43360   DKK     10342140.55    Infinity

2017-03-03      204000      8.88300     NOK     22965.21       Infinity

2017-03-03      41523356    9.51950   SEK     4361926.15     Infinity

2017-03-03      172024902 112.63107 ISK     1527330.86     Infinity

2017-03-02      337162                        EUR     NaN            Infinity

2017-03-02      97530245    7.43330   DKK     13120719.59    Infinity

2017-03-02      228000      8.86180     NOK     25728.40       Infinity

2017-03-02      75064958    9.52680   SEK     7879346.47     Infinity

2017-03-02      231343048  113.37063 ISK     2040590.58     Infinity

2017-03-01      864205                       EUR     NaN            Infinity

2017-03-01      106789016   7.43320  DKK     14366493.03    Infinity

2017-03-01      122000      8.86930     NOK     13755.31       Infinity

2017-03-01      49921016    9.56750   SEK     5217770.15     Infinity

2017-03-01      260890723  113.94813 ISK     2289556.79     Infinity

It looks like the calculated column you provided was close but it is still using the historical exchange rate rather than todays exchange rate for all values.

The measure produces the wrong values as above under column cval4

IS this correct below

CValue3 =
var temp=MAXX(FILTER(ALL('price'),'price'[Date]=EARLIER('asgardprod pnr_price'[Date])&&'price'[EXRATE]=EARLIER('price'[EXRATE])),'price'[EXRATE])
return
'price'[amount] / temp