I have a problem I hope you may help me out, and I strongly believe this could be a good example for other users.
I’ve attached my dummy file for reference.
This situation comes from a real problem I am experiencing in my real job. In our case, product-costs are updated in a quarterly basis, so this means to calculate the real-margin you cannot use “sales_price” - “related_cost” from item_master table.
The point is that “Product_Cost” changes with the time, so you need to define some measures to calculate dinamically the cost for the product for every moment.
The first problem I have to face, is related with the text-format used on dates. Financial Dept provides a table with year-quarter, with text-type as format. Please see above.
Second problem, is how relate the cost for every sale. Sales_Table contains “invoice_date” which is a complete date, fully linkeable with Dates_Table, but it doesn’t contains a “YearQuarter” column.
Could you help me with this problem?, could you give some tips-advices to move forward?