2020: Week 6 Solution
This week's challenge was all about Conversion Rates and calculating the sales value in both GBP and USD.
Our first task is to calculate the best and worst GBP to USD conversion rate on a weekly basis so that we can start to calculate our USA sales value. The first step will be to bring in the GBP to USD conversion rate table, and then use a DATENAME function to find out which week in the year each date falls in.
Week No.
DATENAME('week', [Date])
We will also need to extract the year from the date. Therefore we use a similar calculation on the Date field, but this time instead of date name we can just use the YEAR function.
Year
YEAR([Date])
The next step is to split the British Pound to US Dollar field, so that we have a column for each currency. To do this we have used the custom split tool to split the field at the '=', therefore giving us a column for GBP and USD. We can then rename each column, tidy up the fields by removing any letters, and finally changing the data type to a number (decimal).
We are now ready to calculate the Best and Worst rate for each week within our data set. As we are calculating both the best and worst rate, this means that we will need to duplicate the USD field before using the Aggregate tool (as we can only use a field once within this tool).
Within the aggregate step will want to group by Week and Year, then find the Max USD (best rate) and the Min USD (worst rate). The set up should look like this:
Now we have our weekly values, we are now ready to join this to our Sales table. This can be done with a simple inner join, and joining on Year and Week Number. The join should look like this:
After the join we can then remove any duplicated fields, and then calculate the sales value that our US team have sold. We can calculate this using the Sales Value field and also the US Stock Sold (%) field, with a calculation like this:
US Sales Value
[Sales Value]*([US Stock Sold (%)]/100)
Now we have the US Sales Value, we can then subtract that from the total sales value to calculate how much we have sold in the UK.
UK Sales Value
[Sales Value]-[US Sales Value]
The final step is to convert the US sales into USD from GBP, and find the best and worst rate for each week. We can do this by using the min/max fields from the earlier aggregate and then simply multiplying each US Sales Value by both of them. The calculations will look like this:
US Sales Best (USD)
Round([Best USD]*[US Sales Value],2)
Round([worst USD]*[US Sales Value],2)
Next we need to calculate the variance between the Best and Worst conversion rate each week. This again is a simple calculation by subtracting the Best sales from the Worst sales.
US Sales Potential Variance
round([US Sales Value Best (USD)]-[US Sales Value Worst (USD)],2)
Note, don't forget to round the answers so that they are in a traditional currency format.
The final step is to clean up the fields so that we have the desired output. To do this we need to remove any unwanted fields, and then use a string function to create the desired week field. The calculation for this is:
Week
'wk '+STR([Week No.])+' '+STR([Year])
To understand this calculation we are saying for everything row we want a string to say 'wk', a space, our Week Number, another space, then the Year.
After cleaning up any other unwanted fields we should be left with an output looking like this:
