Show previous value of specific customer

[Anonymous]

Hi everyone,

I would like to make calculations on order dates based on the previous order dates of specific customers.

So far, I've add a "SalesSequence" column in my "Sales" table using this Dax formula:

SalesSequence =

VAR CurrentOrderDate = Sales[OrderDate]
RETURN
COUNTROWS(
    FILTER(
        CALCULATETABLE(
            Sales;
            ALLEXCEPT(Sales;Sales[AccountId])
        );
        Sales[OrderDate] <= CurrentOrderDate
    )
)

This should help me get the previous order date of a specific customer.

Here is an example of the column "PreviousOrderDate" I'd like to add to my "Sales" table:

AccountId	OrderDate	SalesAmount	SalesSequence	PreviousOrderDate
a1	01.01.2015	100	1
a1	12.01.2015	200	2	01.01.2015
b1	23.02.2016	140	1
b1	20.10.2017	50	2	23.02.2016
a1	14.10.2016	100	3	12.01.2015
c1	20.08.2016	200	1
a1	12.10.2017	300	4	14.10.2016

Your help will be much appreciated !

Thanks in advance !

R.

[Vvelarde]

@Anonymous

Hi, try with this calculated column.

PreviousOrderDate =
CALCULATE (
    LASTNONBLANK ( Table1[OrderDate]; Table1[OrderDate] ),
    FILTER (
        Table1,
        Table1[OrderDate] < EARLIER ( Table1[OrderDate] )
            && Table1[AccountId] = EARLIER ( Table1[AccountId] )
    )
)

Regards

Victor

Lima - Peru

[Anonymous]

@Vvelarde

Thanks for your answer.

Is there another way to do it? Because I tried yours and it's still calculating 15 minutes later (there is +3.8M rows in my sales table).

[Vvelarde · Accepted solution]

@Anonymous

Try with this: 

Column 2 =
CALCULATE (
    VALUES ( Table1[OrderDate] ),
    FILTER (
        ALL ( Table1 ),
        Table1[SalesSequence]
            = EARLIER ( Table1[SalesSequence] ) - 1
            && Table1[AccountId] = EARLIER ( Table1[AccountId] )
    )
)

Regards

Victor

[Anonymous]

@Vvelarde

It works like a charm !

Thank you very much. 🙂

R.