Hello everyone,
So to jump directly to the case, what I want to achieve is calculating the currency difference impact from 1 year to another.
CHF: domestique currency
EUR, GBP,…: Foreign currencies
The concept is the following: I calculate the Total CHF sales amount with this formula and it works.
Now what I need to do is recalculate this CHF amount but this time using the previous year currencies. At the moment I was not able to create this measures since the previous formula is using lookup to select the date and currency it has to aply for converting foreign currencies to CHF.
One I’ll have this, it is straight forward has I will only have to substract 1 formula by the oder.
Any ideas 
I guess this topic could interest more than 1 person in the Finance field.
Best regards
Mark
Interesting one, will try to work something up around this and come back to you
Thanks Sam,
with this “Column” formula is was able to put a “lastyeardate” in front of every “currentyeardate”. necessary since during the weekend there is no currency rate and weekend variates between years.
As you can see on the following image for 21.02.2016 there was no date so this formula returns a +1 or +2 date for those specific cases.
Not sure I’m on the right track since lookup doesn’t work in this case.
Best regards
Mark
That’s one way to complete it but not my preferred as I think you should be able to complete this with just DAX formula.
Question…are you placing this against a date context? I can’t tell how you are using this particular function in say a table or a visual.
Also I want to make sure you have connected the date table to this particular table. You need to be doing this.
It would be good to see the model.
Reason is because I not sure you require the last parameters in LOOKUPVALUE. Shouldn’t this just happen naturally with the filtering the would occur from a date table connected via a relationship to your ccy table.
Also by doing this with a date table you could wrap you SumtotalforeignCurr variable inside a simple time intelligence calculation.
Something simple like DATEADD
= CALCULATE( SumtotalforeignCurr, DATEADD( datescolumn, -1, YEAR) )
Hopefully this helps.
If not, being able to see all the variables at play in a resource would be helpful.
Thanks
Ultimately I’d like to use the currencie impact calculation in simple Card ( 2017 sales at 2017 rates - 2017 sales at 2016 rates) & use the 2017 sales at 2016 rates in a table/matrice chart on a monthly level to compare it with 2017 sales at 2017 rates.
The Card visu. seems to be quite complexe as I need to had the currencie and the date context in DAX.
The model is very simple has, at the moment, allmost all the data is in 1 table.
I manage to calcuate this measure in DAX in an other model using a supporting table that didnt take into consideration the LastYearDates. Only comp
aring Actuals rates vs Budget rates.
Regards
Mark
First I would try to improve the look of this model a bit. Only small changes but important I think.
There’s a few relationship and tables scattered around where I think you can organize this more intuitively.
Check out these setup tips.
Still having a hard time getting a true read on what’s required here as can’t see and test the model myself.
I can only add a few suggestions based on a model I have around this also.
See how the below it setup
You’re pretty much there but I would position tables like this.
This is what exchange rate table looks like (I think this is pretty similar to yours)
Once you have a model like this you can use a very simple calc to get the current years exchange rate
Exch. Rate = AVERAGE( 'Exchange Rates'[Exchange Rate] )
Eventhough this is average as there is only one exchange rate every day it will basically just retrieve that singular result.
Then all you need to do for last year is this.
Last Year Exch. Rate =
CALCULATE( [Exch. Rate],
DATEADD( Dates[Date], -1, YEAR ) )
I believe the using the model here is key.
Does this help you solve things?
Hello Sam,
Yes it really helped, thanks a lot!
But obviously I came up with some other issues. >_<
The problem here with this formula is that I m not giving Dax a single date dimension. I need to calculate the average of every single day and of every single currency. I have CHF/EUR, EUR/CHF, GBP/EUR, EUR/GBP,… This both sides convertion is necessary in the company I work for.
In the following screen shot you can see the model and the formula used to creat my metric. Everything works fine when I put it in visuals that contains daily dates and only 1 side conversion. But if for exemple I need to convert everything in CHF for “Company Groupe View” it is averaging EUR, CHF, USD, GBP,… Rates together.
![24|690x152]
So some how, I need to creat within the “Average Ex rates formula” this distinction between currency to calculate and daily average rates by currency (to get that exact same values back).
Final objetive is when an invoice is paid in Swizerland in CHF but it concerns the French Market it will be converted into EUR but using LY rates. The conversion and the reallocation to the correct Market works using cost centers mappings, I build the formula and it works perfectly. The problem is “just” how to create this LY same periode rates table.
And Idea? 
Best regards
Mark
Hello again Sam,
After reflexion I think the easiest way “if its possible” is to creat a new column that put every last year rates in front of actual year rates.
This would work perfectly with my other converting formulas using lookup function.
Would you know how to make this work?
Regards
Mark
There’s a lot happening here…
I don’t believe I can solve this without an example.
Can you attached an example file here to work through this.
I’ve tried to imagine everything at play here, just a bit to difficult. On the face of it, the DAX formula seems way to calculated above.
You should be able to solve this in the model and combined with DAX formulas I believe.
Thanks
Hello Sam,
I’ve been able to get my way trough this problematic and it works perfectly.
Thanks for your support. I’lle be glade to share what I did if you are interested.
Power BI is AWESOME 
Have a nice day
Mark
That’s great. Yes please share, that would be great.
Thanks
