Answered by:
Calculating Discount every year for same dates?
Question

Hello,
I have following Tables and I have difficulty building a DAX function to calculate my discount amount.
dimDates:
DateKey, Year, Month, Day etc etc all obvious columns.
dimSalesmen:
EmpKey, EmpName, EdQual, Age, Gender etc
dimProducts:
ProductKey, Cost, Price, ProdCategoryKey.
FactSales:
OrderID, OrderDate, EmpKey, ProductKey, Customer, Qty, ShippingMode etc
All the above tables are connected with their obvious relationship keys.
The problem starts now with a table dimDiscounts. See the problem below.
dimDiscounts:
DiscountStartDate, DiscountEndDate, ProductKey, DiscountName, Discount%
Suppose I have my Discount dates like Dec/24/2002 to Dec/25/2002, Thinkpad T420 as my ProductKey, Christmas as my DiscountName, 0.15 as my Discount%.
I want a DAX formula which calculates Discount every year with the above dates taking from every year with all the other conditions.
I want to see Product wise Disount, DiscountName wise Sales, Total Discount in a year. Please ask questions if you need anything to solve this.
Thank you all
Arjun Shetty
 Edited by Arjun M Shetty Sunday, September 27, 2015 7:06 AM Spelling
Answers

Hi Arjun,
According to your description, you need to calculate the discount for each year, right?
In your DimDiscounts table, there are DiscountStartDate and DiscountEndDate column, so you can use Year function to get the year for the discounts, and then sum up the discounts for each year.
Year:=YEAR(test0929[DiscountStartDate])
Total:=CALCULATE(SUM(test0929[Discount]),ALLEXCEPT(test0929,test0929[Year]))
If this is not what you want, please elaborate your requirement, and provide us some sample data, so that we can make further analysis.
Regards,
Charlie Liao
TechNet Community Support Proposed as answer by Charlie LiaoModerator Thursday, October 8, 2015 9:19 AM
 Marked as answer by Charlie LiaoModerator Monday, October 12, 2015 2:22 AM
All replies

Hi Arjun,
According to your description, you need to calculate the discount for each year, right?
In your DimDiscounts table, there are DiscountStartDate and DiscountEndDate column, so you can use Year function to get the year for the discounts, and then sum up the discounts for each year.
Year:=YEAR(test0929[DiscountStartDate])
Total:=CALCULATE(SUM(test0929[Discount]),ALLEXCEPT(test0929,test0929[Year]))
If this is not what you want, please elaborate your requirement, and provide us some sample data, so that we can make further analysis.
Regards,
Charlie Liao
TechNet Community Support Proposed as answer by Charlie LiaoModerator Thursday, October 8, 2015 9:19 AM
 Marked as answer by Charlie LiaoModerator Monday, October 12, 2015 2:22 AM

Hey Charlie,
Forget the above model..Just think generally.
I need to calculate sales for a specific date range like:
Jan 01 to Jan 15 ......... "Pongal Sales"
Jan 01 to Jan 01........."New Year Sales"
Dec 25 to Dec 25..........."Christmas Sales"
And slice these sales by years.
I tried this but not working:
PongalSales :=
CALCULATE (
[TotalSales],
DATESBETWEEN (
DimDate[Datekey],
FIRSTDATE ( DimDate[Datekey] ),
FIRSTDATE ( DimDate[Datekey] ) + 15
)
)I tried this also, not working:
PongalSales :=
CALCULATE (
[TotalSales],
DATESBETWEEN (
DimDate[Datekey],
DATE ( YEAR ( DimDate[CalendarYear] ), 1, 1 ),
DATE ( YEAR ( DimDate[CalendarYear] ), 1, 15 )
)
)
This is working, but I can't slice this by Years....got it..?
PongalSales :=
CALCULATE (
[TotalSales],
DATESBETWEEN ( DimDate[Datekey], "1/1/2007", "15/1/2007" )
)
ArjunMShetty
 Edited by Arjun M Shetty Thursday, August 23, 2018 1:59 PM