locked
Dates of Price Changes per Article RRS feed

  • Question

  • Hi,

    I have a table with the following columns:

    • Date
    • ArticleID
    • OriginalPrice
    • NowPrice
    • PriceReduction

    I'd like to create calculated columns as follows:

    • the date of last/first price reduction per ArticleID
    • the date of lowest/highest NowPrice per ArticleID
    • the number of price changes (i.e. different price reductions) per ArticleID

    I could create calculated columns to calculate lowest/highest NowPrice per ArticleID. But I don't know how to solve the questions above.

    I enclose a dropbox link to an example Excel 2013 file: PP_PriceChange.xlsx

    Thank you for any advice!

    Chiemo

    Sunday, November 30, 2014 8:54 PM

Answers

  • I've updated the workbook and uploaded it here.

    My solutions:

    Number of price changes: I misunderstood this due to the structure of the table. It is now acting appropriately:

    =
    COUNTROWS(
        CALCULATETABLE(
            SUMMARIZE( 
                Table1
               , Table1[NowPrice]
               , Table1[PriceReduction]
            )
            , ALLEXCEPT( Table1, Table1[ArticleID] )
        )
    )

    Date of last price reduction: This needs some helper columns:

    PriorDate=
    CALCULATE(
        MAX( Table1[Date] )
        , ALLEXCEPT( Table1, Table1[ArticleID] )
        , Table1[Date] < EARLIER( Table1[Date] )
    )
    
    PriorNowPrice=
    CALCULATE(
        VALUES( Table1[NowPrice] )
        , FILTER( 
            ALL( Table1 )
            , Table1[ArticleID] = EARLIER( Table1[ArticleID] )
                && Table1[Date] = EARLIER( Table1[PriorDate] )
        )
    )
    
    LastPriceChange=
    CALCULATE(
        Max( Table1[Date] )
        , FILTER(
            ALLEXCEPT( Table1, Table1[ArticleID] )
            , Table1[NowPrice] <> Table1[PriorNowPrice]
        )
    )

    First date of lowest price: I was just stupid before:

    LowestPriceDate=
    CALCULATE(
        MIN( Table1[Date] )
        , FILTER(
            ALLEXCEPT( Table1, Table1[ArticleID] )
            , Table1[NowPrice] = EARLIER( Table1[MinNowPrice] )
        )
    )

    • Marked as answer by Chiemo Wednesday, December 17, 2014 9:08 PM
    • Unmarked as answer by Chiemo Wednesday, December 17, 2014 9:09 PM
    • Marked as answer by Chiemo Wednesday, December 17, 2014 9:09 PM
    Tuesday, December 16, 2014 12:04 AM

All replies

  • Can you provide an example of the table and what the results should look like.  I am working in Excel 2010, so I can't look at your work.

    Wednesday, December 10, 2014 8:43 PM
  • Try this workbook: Updated workbook

    I am not copying over all of the measures below, but I am doing just a couple that should cover the methods I used (there's a lot of duplication).

    MaxNowPrice=
    CALCULATE(
        MAX(Table1[NowPrice])
        , ALLEXCEPT( Table1, Table1[ArticleID] )
    )

    Very similar to your original, but terser, faster (only visible on very large data sets), and can easily be transferred to a measure.

    ALLEXCEPT() is the hero here. It takes a table as its first argument a table, and returns that table stripped of the columns that you pass as arguments 2-N. Additionally, it ignores all context filters except for those on the columns you specify in arguments 2-N. Thus in this context it will return all of Table1 (but not the [ArticleID] column) where the (omitted) [ArticleID] value is equal to the current [ArticleID] value. Evaluated on a row this returns a table that only has the values associated with the [ArticleID] on that row.

    FirstReduction=
    CALCULATE(
        MIN( Table1[Date] )
        , ALLEXCEPT( Table1, Table1[ArticleID] )
        , Table1[PriceReduction] < 0
    )

    As above, but looking at the date in this case. We also add a second filter argument to the CALCULATE() restricting the result to rows where there is reduction in price (your column naming and wording in your post make it ambiguous whether you are referring to the first date with a decrease in the value of the price, or the first date where there is a value for the column [PriceReduction] - you can omit this filter argument if you need).

    LowestPriceDate=
    CALCULATE(
        MIN( Table1[Date] )
        , ALLEXCEPT( Table1, Table1[ArticleID], Table1[MinNowPrice] )
    )

    Here, we see an example where we pass multiple columns to ALLEXCEPT(). Now we respect whatever the MinNowPrice is for the current article (the article on the row in which we evaluate this expression) in addition to respecting the [ArticleID].

    NumberChanges=
    COUNTROWS(
        CALCULATETABLE(
            Table1
            , ALLEXCEPT( Table1, Table1[ArticleID] )
        )
    )

    Here we call COUNTROWS() on CALCULATETABLE(). The table we calculate is all of Table1 where the [ArticleID] is equal to the [ArticleID] in context (again, since this is a calculated column, the current context is the current row).

    You might think you could just do COUNTROWS( ALLEXCEPT(...)), or at least I did. This leads to an interesting circular dependency error. I tried to counter by also including [NumberChanges] in the call to ALLEXCEPT() and the circular dependency error disappeared, but the measure returned a count of the rows in the whole table. This is something that probably merits greater attention than I can commit to it right now.

    Let me know if these work, or if you need help changing them.

    • Proposed as answer by Michael Amadi Wednesday, December 10, 2014 9:54 PM
    Wednesday, December 10, 2014 9:52 PM
  • Hello Greg2178,

    thank you for your advice and for providing the updated workbook. The allexcept() function is indeed very interesting and helpful.

    I am still a newbie with powerpivot and learn day by day and challenge by challenge.

    I checked the measures and some are unfortunately not working as expected.

    In order to make it more obvious what I am looking for I repeat the core table columns of table1:

    Date      ArticleID   OriginalPrice   NowPrice  PriceReduction

    11/1      1001         100               50              -50%
    11/26    1001         100               50              -50%
    11/27    1001         100               50              -50%
    11/29    1001         100               50              -50%

    11/1      1002          50                40              -20%
    11/26    1002          50                30              -40%
    11/27    1002          50                20              -60%
    11/29    1002          50                25              -50%

    11/1      1003          20                30              +50%
    11/26    1003          20                20                  0%
    11/27    1003          20                10               -50%
    11/29    1003          20                20                  0%

    Let me concentrate on the biggest challenges for me:

    Number of Price Changes (compared to original or previous price) per ArticleID:

    1. the correct answer for article 1001 would be = 1, for 1002 = 4, for 1003 = 4
    2. formula provided in your workbook gives 4 price changes for any article
    3. I would think to solve this by 3 different steps, i.e. calculated columns:

      Step1:
      Calculate the previous price reduction. Unfortunately the following formula does not work (I think because of the Max() function; see column "FormerPriceReduction" in  updated model):
      =CALCULATE(MAX(Table1[PriceReduction]);FILTER(Table1; EARLIER(Table1[ArticleID])=Table1[ArticleID] && EARLIER(Table1[Date])>Table1[Date]))

      Step2:
      Check if current price reduction differs from previous price reduction; if yes = 1, if no = 0; formula:
      =IF([PriceReduction]-[FormerPriceReduction]<>0;1;0)

      Step3
      : Sum of all changes under Step2 = number of price changes. Formula:
      =CALCULATE(SUM([PriceChange=1]);FILTER(Table1;Table1[ArticleID]=EARLIER(Table1[ArticleID])))

      Please see updated excel workbook :
      https://www.dropbox.com/s/g075xhhbo7jyttw/PP_PriceChangeUpdate.xlsx?dl=0

    Date of Last Price Reduction (i.e. date of last price change) per ArticleID:

    1. The correct answer would be for article 1001 = 11/01, for 1002 = 11/29, for 1003 = 11/29
    2. The formula provided delivers for 1001 = 11/29, for 1002 = 11/29 and for 1003 = 11/27
    3. I would think that the use of the correct formula from Step1 above could be a starting point for getting the correct answer

    (First) Date of Lowest Price:

    1. The correct answer would be for article 1001 = 11/01, for 1002 = 11/27, for 1003 = 11/27
    2. I think the formula provided gets the wrong results because it relates to the column „MinNowPrice“ which lists for each date the all time lowest price
    3. Do you have any ideas to solve this problem?

    Sorry that I cannot contribute much to the solution. I hope you can give me some more hints.

    Thank you very much

    Chiemo
    Thursday, December 11, 2014 4:56 PM
  • Hello, johngabbradley1,

    please see reply to Greg2178.

    Thank you

    Chiemo

    Thursday, December 11, 2014 5:01 PM
  • I've updated the workbook and uploaded it here.

    My solutions:

    Number of price changes: I misunderstood this due to the structure of the table. It is now acting appropriately:

    =
    COUNTROWS(
        CALCULATETABLE(
            SUMMARIZE( 
                Table1
               , Table1[NowPrice]
               , Table1[PriceReduction]
            )
            , ALLEXCEPT( Table1, Table1[ArticleID] )
        )
    )

    Date of last price reduction: This needs some helper columns:

    PriorDate=
    CALCULATE(
        MAX( Table1[Date] )
        , ALLEXCEPT( Table1, Table1[ArticleID] )
        , Table1[Date] < EARLIER( Table1[Date] )
    )
    
    PriorNowPrice=
    CALCULATE(
        VALUES( Table1[NowPrice] )
        , FILTER( 
            ALL( Table1 )
            , Table1[ArticleID] = EARLIER( Table1[ArticleID] )
                && Table1[Date] = EARLIER( Table1[PriorDate] )
        )
    )
    
    LastPriceChange=
    CALCULATE(
        Max( Table1[Date] )
        , FILTER(
            ALLEXCEPT( Table1, Table1[ArticleID] )
            , Table1[NowPrice] <> Table1[PriorNowPrice]
        )
    )

    First date of lowest price: I was just stupid before:

    LowestPriceDate=
    CALCULATE(
        MIN( Table1[Date] )
        , FILTER(
            ALLEXCEPT( Table1, Table1[ArticleID] )
            , Table1[NowPrice] = EARLIER( Table1[MinNowPrice] )
        )
    )

    • Marked as answer by Chiemo Wednesday, December 17, 2014 9:08 PM
    • Unmarked as answer by Chiemo Wednesday, December 17, 2014 9:09 PM
    • Marked as answer by Chiemo Wednesday, December 17, 2014 9:09 PM
    Tuesday, December 16, 2014 12:04 AM
  • Hi Greg2178,

    perfect! All your formulas work! Great!

    The formula "number of price changes" provides the number of different prices. That's fine for me!
    There are however cases where the number of price changes is higher than the number of different prices. Example: price1 = 20, p2 =10, p3 = 5, p4 =10, p5 = 5; that means 3 different prices (20,10,5) but 4 price changes.

    Again thank you very much. Without your help I would have been lost.

    Chiemo

    P.s. Sorry for marking/unmarking/marking your reply as answer. I struggled with the forum functionality. It's definately a well earned answer!

    Wednesday, December 17, 2014 9:21 PM
  • For [Number of Price Changes], this function is actually computing the number of unique combinations of [NowPrice] and [PriceReduction] that belong to the [ArticleID] on the current row. Just change the arguments 2-N of SUMMARIZE() (everything after the table name on its own) to whatever group of columns you need to consider unique combinations across.
    Wednesday, December 17, 2014 9:40 PM