How to calculate discount in PowerPivot??

Hi all,

This is a cross post from MrExcel.Com where I have no luck in getting a reply for this.

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 to calculate Row level discount in fSalesBook??
Thank you so much for your help!

mrxlsx