Time Intelligence DTD

[Pentanol]

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

[Zubair_Muhammad · Accepted solution]

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

[Zubair_Muhammad · Accepted solution]

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

[Zubair_Muhammad · Accepted solution]

@Pentanol

See the pic below.I just added another SKU to your sample data