Sales Report:
I have a database with Sales Summary information per event (Exhibitions). A new Sales Summary information file (report) gets uploaded on a weekly basis. The Summary information is posted in a foreign currency. I also have a currency exchange rate table with various rate types (Budget rates, month average rates, month end rates, etc).
I need to have the foreign currency converted to GBP (the Global Base Currency) using the SalesBudget rate for the date of the summaryDate if the summaryDate date is before the EventEndDate, but if the summaryDate is after the Event End date, then the MonthAverage rate should be used IF it is available, if not then keep using the SalesBudget rate.
Here is another description that was relayed to me by our finance team:
For Sales reports – we are using budget rate if event didn’t happen at the reporting date and monthly average rate after the event end date WHEN monthly average rate is available. For example event finished at 01st September, so we should use September monthly average rate, however it will be available only on 1st October, before that budget rate will be used.
Reporting date is when sales summary was submitted.
Hello,
I can build the formula you want in Power BI as i am an experienced Power Bi developer with over 8 years in the field.
Please check my portfolio to see my work.
Thanks!