rkspeaks
Member
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, fSaleBook .
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 fSaleBook, not just for that year in the dDiscount table.
So in essence, when you calculate discount for a product in fSaleBook 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.
I have the following relationships between the tables.
Table [Column] ------->Table [Column]
fSalesBook[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 fSalesBook. You can change the relationships if there's need be.
And here is the link for the sample workbook.
https://drive.google.com/file/d/0B6G...ew?usp=sharing
What should be my DAX formula to calculate Row level discount in fSalesBook??
Thank you so much for your help!
rkspeaks
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, fSaleBook .
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 fSaleBook, not just for that year in the dDiscount table.
So in essence, when you calculate discount for a product in fSaleBook 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.
I have the following relationships between the tables.
Table [Column] ------->Table [Column]
fSalesBook[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 fSalesBook. You can change the relationships if there's need be.
And here is the link for the sample workbook.
https://drive.google.com/file/d/0B6G...ew?usp=sharing
What should be my DAX formula to calculate Row level discount in fSalesBook??
Thank you so much for your help!
rkspeaks