Identify no purchase after specific date or no purchase at all

[Anonymous]

Hi,

I want to identify the customers that did not have a purchase on a certain product group for longer than 4 months or not at all.

There is already a topic regarding this: Topic I created

In this topic I thought I found the answer, however I am running in to a new problem.

I would like to identify the customers that did not have a purchase on a certain product group for more than 4 months or not at all. Due to merged tables with quarterly data it could happen A customer has no purchase on a product group, however further down the table it has.

I am using the following data:

Customer	Product group	product	date purchase	Amount	Price	No regular purchase
Customer A	A
Customer A	A
Customer B	A
Customer B	B
Customer A	A	AA	11-8-2018	2	5
Customer A	A	AB	11-8-2018	5	28
Customer A	B	BA	11-8-2018	4	45
Customer A	B	BB	11-8-2018	33	688
Customer A	B	BB	25-8-2018	2	45
Customer A	C					No purchase
Customer A	D					No purchase
Customer B	A	AA	21-7-2018	2	68
Customer B	A	AB	21-7-2018	6	33
Customer B	A	AB	4-8-2018	4	78
Customer B	A	AD	4-8-2018	8	93
Customer B	B	BA	4-8-2018	4	54
Customer B	B	BB	4-8-2018	2	12
Customer B	C					No purchase
Customer B	D	DA	21-12-2017	8	85
Customer B	D	DB	11-1-2018	6	43	No purchase
Customer C	A					No purchase
Customer C	B	BA	6-4-2016	4	93
Customer C	B	BB	11-4-2016	5	5	No Purchase
Customer C	C	CA	3-10-2016	6	72
Customer C	C	CB	6-10-2016	4	47
Customer C	C	CC	11-10-2016	8	32	No purchase
Customer A	A
Customer A	A
Customer A	B
Customer A	B
Customer A	B
Customer A	C
Customer A	D
Customer A	E					No purchase
Customer A	E
Customer A	E
Customer A	F					No purchase
Customer A	F
Customer A	F
Customer B	A
Customer B	B
Customer B	C
Customer B	D
Customer B	E					No purchase

In my previous topic we identified the following DAX:

NEW = 
VAR minIndex =
    CALCULATE (
        MIN ( Table3[Index] ),
        ALLEXCEPT ( Table3, Table3[Product group], Table3[Customer] )
    )
VAR lastestDate =
    CALCULATE (
        MAX ( Table3[date purchase] ),
        ALLEXCEPT ( Table3, Table3[Product group], Table3[Customer] )
    )
VAR PGHasPur =
    CALCULATETABLE (
        VALUES ( Table3[Product group] ),
        FILTER ( ALLEXCEPT(Table3, 'Table3'[Customer] ), Table3[date purchase] >= EDATE ( TODAY (), -4 ) )
    )
RETURN
    IF (
        ISBLANK ( [date purchase] ),
        IF ( [Index] = minIndex, "No purchase", BLANK () ),
        IF (
            NOT [Product group] IN PGHasPur
                && [date purchase] = lastestDate,
            "No purchase",
            BLANK ()
        )
    )

The result in powerBI:

As you can see the first row is marked as "no purchase", however row 3 and 4 indicate there has been a purchase in the last 4 months on product group A.

I hope someone can help me finding a solution.

If any further questions please do not hesitate to ask.

Kind regards,

Guido

[Anonymous]

Hi,

I have pasted your sample data and added a calculated column to determin if the customer has purchased a product group which will give 3 outputs

1) Not purchased ever.

2) Not purchased within last four months.

3) Purchased within last four months.

Can you try this?

New =
IF (
    SUMX (
        FILTER (
            ALL ( Table1 ),
            EARLIER ( Table1[Customer] ) = Table1[Customer]
                && EARLIER ( Table1[Product group] ) = Table1[Product group]
        ),
        Table1[Amount]
    )
        = 0,
    "Not purchased ever",
    IF (
        SUMX (
            FILTER (
                ALL ( Table1 ),
                EARLIER ( Table1[Customer] ) = Table1[Customer]
                    && EARLIER ( Table1[Product group] ) = Table1[Product group]
                    && Table1[date purchase] >= EDATE ( TODAY (), -4 )
            ),
            Table1[Amount]
        )
            = 0,
        "Not purchased in last 4 months",
        "Purchased within 4 months"
    )
)

[Anonymous]

@Anonymous

Could you think of a way to make that happen?

Regards,

Guido

[Anonymous]

Hi,

Can you clarify the follwing..

1) Do you want the results in a new calculated table or in the same table itself.

2) In your sample data you have posted, the "Index" column is not there, but in the previous formula it has been used. Can you post the sample data again with all the relevant columns included. 

3) There are 3 scenarios:

    a) Customer has not purchased the product group ever.

    b) Customer has not purchased the product group within last four months.

    c) Customer has purchased the product within last months.

Once you clarify these things, I think we can bring the desired output.

In each of these scenarios (a,b or c), what is the value you want in the "New" column?

[Anonymous]

Hi @Anonymous

Thanks for you reply 

1) I would like the results in the same table (column "no regular purchase" is the desired custom column)

2) The index colomn is added in the query editor. I have added the index colomn in the sample data below.

3) There are indeed 3 scenarios

 a) The result would be: "No purchase". However, this will occure only once per product group per customer

 b) The result would be: "No purchase". However, this will occure only once per product group per customer

 c) The result would be: blank 

The outcome would be as column "no regular purchase" marked in red:

Customer	Product group	product	date purchase	Amount	Price	Index	No regular purchase
Customer A	A					0
Customer A	A					1
Customer B	A					2
Customer B	B					3
Customer A	A	AA	11-8-2018	2	5	4
Customer A	A	AB	11-8-2018	5	28	5
Customer A	B	BA	11-8-2018	4	45	6
Customer A	B	BB	11-8-2018	33	688	7
Customer A	B	BB	25-8-2018	2	45	8
Customer A	C					9	No purchase
Customer A	D					10	No purchase
Customer B	A	AA	21-7-2018	2	68	11
Customer B	A	AB	21-7-2018	6	33	12
Customer B	A	AB	4-8-2018	4	78	13
Customer B	A	AD	4-8-2018	8	93	14
Customer B	B	BA	4-8-2018	4	54	15
Customer B	B	BB	4-8-2018	2	12	16
Customer B	C					17	No purchase
Customer B	D	DA	21-12-2017	8	85	18
Customer B	D	DB	11-1-2018	6	43	19	No purchase
Customer C	A					20	No purchase
Customer C	B	BA	6-4-2016	4	93	21
Customer C	B	BB	11-4-2016	5	5	22	No Purchase
Customer C	C	CA	3-10-2016	6	72	23
Customer C	C	CB	6-10-2016	4	47	24
Customer C	C	CC	11-10-2016	8	32	25	No purchase
Customer A	A					26
Customer A	A					27
Customer A	B					28
Customer A	B					29
Customer A	B					30
Customer A	C					31
Customer A	D					32
Customer A	E					33	No purchase
Customer A	E					34
Customer A	E					35
Customer A	F					36	No purchase
Customer A	F					37
Customer A	F					38
Customer B	A					39
Customer B	B					40
Customer B	C					41
Customer B	D					42
Customer B	E					43	No purchase

Hope this will make it more clear.

If any questions please ask.

Kind regards,

Guido

[Anonymous]

@Anonymous

Can you find a solution?

Regards,

Guido

[Anonymous]

Hi @Anonymous,

Thanks for your reply.

I would prefer to have only 1 mark per product group per customer as shown in the other DAX.

Regards,
Guido