Dear all
I've been scratching my head for quite a bit on this.
In a nutshell:
- Objective - building a 'time-to-delivery' view between Sales Orders and corresponding Delivery Notes at individual product level.
- If there is a delivery note date, my time to delivery is calculated as: Delivery Note Date minus Sales Order date
- If there is no delivery note date yet (Sales Order has not yet been delivered), my time to delivery is TODAY() minus Sales Order date.
- Sales Order table and Delivery Note table is a one-to-many relationship. Linked via Key (concatenated string of Sales Order ID & Sales Order Line ID)
- Sales Order table has more records than Delivery Note table (there are new Sales Orders that do not have deliveries yet naturally).
I dont think I'm able to create a calculated column in either of the tables, reasons:
- Cannot create a column in Delivery Note table because there are Sales Orders that have no Delivery Notes yet.
- Cannot create a column in Sales Order Table because some Order lines can have multiple delivery notes with different dates (showed in the sample data).
I would need to use this 'time to delivery' figure in various visuals.
How can I best achieve this?
I created some sample data that mimicks my actual model.
Sample data + pbix can be found here.
