I am building a report, and my Data Model is causing me a headache
I have 6 Tables
Promo's TW - Columns: PromoID, Discount %, Start Date, End Date and Dept
All Promo Tables (all promos in the DB) - Columns: PromoID, SKU, SKU Price, Start Date, End Date
SKU Price History - Columns: Date, SKU, Lowest Price, Dept
Date Table (Unique Dates)
SKU Table (Unique SKU's)
DeptTable - Department Id, Est. Days
The ID is that For each Promo in PROMO TW i want to show:
The SKU's contained in the Promo
the Start Date
Measure: Lowest Price for that SKU based for dates between 'Start Date minus Est.Days' and 'Start Date minus 1)
SKU Promo Price (from the All Promos Table)
SKU Promo Price divided by the LOWEST PRICE Measure above
Below is a rough sketch of the Model I have
