none
Calculating Discount every year for same dates? RRS feed

  • 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
    Sunday, September 27, 2015 7:05 AM

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

    Tuesday, September 29, 2015 2:06 AM
    Moderator

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

    Tuesday, September 29, 2015 2:06 AM
    Moderator
  • 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



    Thursday, August 23, 2018 1:20 PM