How to Calculate my discount in PowerPivot?

[BeepBeep]

Hi all,

I have a problem with calculating discount in PowerPivot analysis. I have dCalendar , dDiscount, fSa*****ok .

dDiscount columns:

DiscKey, StartDate, EndDate, Product, DiscountName, DiscountPercent

fSalebook columns:

OrderDate, Product, Qty, Sales, Discount

dCalendar columns:

Date, Holiday, MonthNo, MonthName, QuarterNo, QrtName, Year.


The problem (at least I feel) is I defined some dates in the Startdate and Enddate for a particular year. But these discounts should apply for all of the years in my fSa*****ok, not just for that year in the dDiscount table.

So in essence, when you calculate discount for a product in fSa*****ok you should consider only the month and day of the transaction, if the sale is in between the discount dates.

PS: One more clause, Sometimes I offer discount only for a particular product in that Discount season, that's why there is a column for Product in the dDiscount table.

What should be my DAX formula??
Thank you so much for your help!
BeepBeep

 

[BeepBeep]

Hi,

Here is the link for the sample workbook. I have the following relationships.


Table [Column] ------->Table [Column]

fSa*****ok[OrderDate] -----> dCalendar[Date]

I am not sure how to relate the dDiscount table with the dCalendar table and not sure even there is a need to relate them to calculate Row level discount in fSa*****ok. You can change the relations/model if there is need be.


https://drive.google.com/file/d/0B6G...ew?usp=sharing

mrxlsx