0

I'm working on a BI project with Visual Studio and Power BI. I get the sales of several stores and in Visual Studio I made a column calculated with the following formula :

=
SWITCH (
    TRUE(),
    Y2_Sales' [storename] = "UK-RC O/O NEWQUAY", Y2_Sales [SalesExGST] / 0.8564058469475494,
    'Y2_Sales' [SalesExGST]
)

To explain I get the sales in GBP and as soon as the store is Newquay I convert these values into euros to display them in my Power BI report.

The problem is that the value I put in hard is a value that moves every week, you can find it here :

https://www.xe.com/fr/currencycharts/?from=EUR&to=GBP&view=1D

Is it possible to modify this value dynamically by having historical exchange rates?

Theo27
  • 385
  • 4
  • 17
  • You can think about a configuration table with week number and currency type/amount. Then join this table with fact table using week number and that case you can get all historical exchange details per row in your table/data. – mkRabbani Nov 17 '20 at 09:24
  • I have a date table with the dates of each week, should I add the exchange rate in this table? @mkRabbani – Theo27 Nov 17 '20 at 10:17
  • If you can do that, you can then use that value using week number for calculation. – mkRabbani Nov 17 '20 at 10:20

1 Answers1

1

I don't quite understand why you want a calculated column, wouldn't a measure for euros be easier? Then you could declare your change rate as another measure and make the whole thing dynamic. You may need to use a disconnected table and selected measures to get the result you want, have a look at sumproduct for that https://www.sumproduct.com/blog/article/power-pivot-principles/ppp-variables-and-disconnected-tables-in-power-bi it looks like you're most of the way there, just lose the calculated column and go all measure!

MikeAinOz
  • 126
  • 1
  • 10
  • 24
  • I make a calculated column because I have all the stores already in euros and the only one I have in GBP is Newquay, so I made a condition, if it's Newquay convert with the exchange rate and if not leave the values as they are. I'll look then – Theo27 Nov 17 '20 at 10:05
  • I see your problem now, I'd create a separate conversion table and do it in Power Query before you get to the report, much cleaner. – MikeAinOz Nov 19 '20 at 05:57
  • Yes I make a table with week and corresponding exchange rate and I read it with the one of my stores? – Theo27 Nov 19 '20 at 10:07
  • Sorry Patrick, is that a question? I would pull the store out that you want to adjust, merge it with the rate and apply the rate, then merge it back into the main data. Do it in little steps :-) – MikeAinOz Nov 22 '20 at 06:20