Here’s Amdi’s entry for Power BI Challenge 9. @Amdi, feel free to add other details of your work.
Here’s how Amdi described it:
This is my submission.
When I saw the challenge I though, now I have the possiblity to learn how to calculate with currency. And Indeed, there was a lot of currency.
Out from the model in the description I build my model with the three currency tables, which relate to the three amounts Purchase, Haulage and Sale. This solution helps me a lot.
I calculated the new currency on the date (Delivery Date).
Created a currency table Where | Pund to USD | Pund to EURO | EURO to USD | EURO to Pound | USD to Pound| USD to EURO.
When I worked through, I realized that when Purchase-Pound, Haulage-Pound and Sale-EURO on the same row. When calculate Purchase and Haulage to EURO I did not get Sale- EURO to EURO. I handled this by expanding the currency table with the three currency. Where the rate was 1 for EURO to EURO | Pound to Pound | USD to USD |.
Example on a calculation for Ref-001 just for one quantity.
Formula= Sale – Purchase – Haulage
Result = -60 – (-112 + 600)= -548
In my calculations I only used Est. Weight