Need help in writing DAX query for finding customer previous product.

[Anonymous]

Hello Commuinty, 

Can you please help me writing following DAX code please.  I need to find the customer previous purchased. 

I tried with previous day since  transaction date is not contnious for customer it is not working properly. here is my code

Previous Product Test =

CALCULATE(

DISTINCT(test[Product Type]),

PREVIOUSDAY(test[date]) ,

ALLEXCEPT(test,test[Customer ID]))

I would profoundly appreciate for any given help.

Many Thanks,

C. A

Customer ID	Product Type	date	Expected Outcome - Previous Product	Expected Outcome - Product Switch
12	F	1-Feb-21
12	F	5-Feb-21	F	F->F
12	NF	3-Mar-21	F	F->NF
12	NF	4-Mar-21	NF	NF->NF
12	F	6-Jul-21	NF	NF->F
12	F	7-Jul-21	F	SameDay
12	F	7-Jul-21	F	SameDay
12	NF	8-Jul-21	F	SameDay
12	F	8-Jul-21	NF	SameDay
10	NF	9-Jul-21
10	F	10-Jul-21	NF	NF->F
10	F	11-Jul-21	F	F->F

[VahidDM · Accepted solution]

Hi @Anonymous 

If you need a measure, try this:

Previous Product = 
VAR _D =
    MAX ( 'Table'[date] )
VAR _PD =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[date] < _D )
    )
VAR _A =
    CALCULATE (
        MAX ( 'Table'[Product Type] ),
        FILTER ( ALL ( 'Table' ), 'Table'[date] = _PD )
    )
RETURN
    IF ( ISBLANK ( _A ), "-", _A )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Hi @VahidDM ,

I just noticed  in your solutions that measure is returning previous product on first purchased date  from another customer ID .  For example Customer ID 10  previous product  on  9th July 2021 is NF whereas this is not the case. Can You please suggest way around it. 

Also, I have multipile years' data. It is not generating accurate results when year is changing.  I am badly stuck in writing this measure  and will really appreciate if you could help  me with it.  

I am ok with both measure or columns. 

Thank you so much for your assistance.,

C. 

Customer ID	Product Type	date	Previous Product ( Expected Results)	Product Switch ( Expected Results )
12	F	1-Feb-21
12	F	5-Feb-21	F	F->F
12	NF	3-Mar-21	F	F->NF
12	NF	4-Mar-21	NF	NF->NF
12	F	6-Jul-21	NF	NF->F
12	F	6-Jul-21	F	F->F
12	F	7-Jul-21	F	F->F
12	NF	8-Jul-21	F	F->NF
12	F	8-Jul-21	NF	NF->F
10	NF	10-Feb-19
10	NF	12-Dec-20	NF	NF->NF
10	NF	9-Jul-20	NF	NF->NF
10	F	9-Jul-20	NF	NF->F
10	F	10-Aug-21	F	F->F
10	F	11-Sep-21	F	F->F
13	F	16-Sep-19
13	NF	25-Feb-20	F	F->NF
13	F	3-Dec-20	NF	NF->F
13	NF	1-Jan-21	F	F->NF
13	NF	31-Aug-21	NF	NF->NF
13	F	1-Sep-21	NF	NF->F
13	F	9-Sep-21	F	F->F

[ValtteriN]

Hi,

Your idea was very close. This seemed to work for me:
Test Data:

Dax:

Previous Product C =

CALCULATE(max('Previous Purchase'[Product]),ALLEXCEPT('Previous Purchase','Previous Purchase'[CID]),'Previous Purchase'[Dates]<EARLIER('Previous Purchase'[Dates]))

End result:

I hope this helps and if it does consider accepting this as a solution!