Forum Discussion

Pentanol's avatar
Pentanol
Frequent Visitor
8 years ago
Solved

Time Intelligence DTD

Hi all,

I searched for this and couldn't find any similar to my situation. I'm tracking a competitors price for all similar products every day as well as our own prices. Trying to come up with a calculated column where I can determined the whether there is a change in price difference between our price and the competitor price each day

 

e.g. for one SKU

Date               Our price    comp price       difference     change in difference DTD

01/01/17        17                       18                 1                  1 (0 to 1)

02/01/17        17                       18                 1                  0 (1 to 1)

03/01/17        17                       17                 0                  1 (0 to 1)

 

I want to be able to check everyday using the change in price difference DTD as a filter/slicer all the products where the competitor price have changed.

 

So this calculated column should show the change in difference based on current data at the row level and also filtered for the the SKU.

 

Cheers,

Andrew

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    HI Pentanol

     

    Andrew

     

    Try this solution

     

    First Add a calculated Column which will RANK the dates for each SKU

     

    Date RANK =
RANKX (
    FILTER ( ALL ( Table1 ), Table1[SKU] = EARLIER ( Table1[SKU] ) ),
    Table1[Date],
    ,
    ASC,
    DENSE
)

    Now you can get the Change in Difference using this Calculated Column

     

    Change_In_Difference =
VAR previousDayDifference =
    CALCULATE (
        SUM ( Table1[Comp Price] ) - SUM ( Table1[Our Price] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[SKU] ),
            Table1[Date RANK]
                = EARLIER ( Table1[Date RANK] ) - 1
        )
    )
VAR CurrentDayDifference = Table1[Comp Price] - Table1[Our Price]
RETURN
    CurrentDayDifference - previousdayDifference

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    HI Pentanol

     

    Andrew

     

    Try this solution

     

    First Add a calculated Column which will RANK the dates for each SKU

     

    Date RANK =
RANKX (
    FILTER ( ALL ( Table1 ), Table1[SKU] = EARLIER ( Table1[SKU] ) ),
    Table1[Date],
    ,
    ASC,
    DENSE
)

    Now you can get the Change in Difference using this Calculated Column

     

    Change_In_Difference =
VAR previousDayDifference =
    CALCULATE (
        SUM ( Table1[Comp Price] ) - SUM ( Table1[Our Price] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[SKU] ),
            Table1[Date RANK]
                = EARLIER ( Table1[Date RANK] ) - 1
        )
    )
VAR CurrentDayDifference = Table1[Comp Price] - Table1[Our Price]
RETURN
    CurrentDayDifference - previousdayDifference