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

Answer 1

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

Answer 2

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