Solved: Re: Identify no purchase after specific date or no...

Anonymous · ‎06-12-2018

Hi,

I would like to create a calculated column, which identifies if a "product group" has not been purchased in the last 4 months or have never been purchased at all.

I have the following date table (example) with the desired calculated column in red:

Customer	Product group	product	date purchase	Amount	Price	No regular purchase
Customer A	A	AA	11-may-18	2	5
Customer A	A	AB	11-may-18	5	28
Customer A	B	BA	11-may-18	4	45
Customer A	B	BB	11-may-18	33	688
Customer A	B	BB	25-may-18	2	45
Customer A	C					No purchase
Customer A	D					No purchase
Customer B	A	AA	21-apr-18	2	68
Customer B	A	AB	21-apr-18	6	33
Customer B	A	AB	04-may-18	4	78
Customer B	A	AD	04-may-18	8	93
Customer B	B	BA	04-may-18	4	54
Customer B	B	BB	04-may-18	2	12
Customer B	C					No purchase
Customer B	D	DA	21-dec-17	8	85
Customer B	D	DB	11-jan-18	6	43	No purchase
Customer C	A					No purchase
Customer C	B	BA	6-apr-18	4	93
Customer C	B	BB	11-apr-18	5	5
Customer C	C	CA	24-may-16	6	72
Customer C	C	CB	06-oct-16	4	47
Customer C	C	CC	21-feb-17	8	32	No purchase

The difficulty for me lies in the fact that if a product group has not been purchased in the last 4 months only the last purchase must be marked as "No purchase"

I hope someone can help me creating the right DAX

Regards,

Guido

v-jiascu-msft · ‎06-20-2018

We need to make a few changes indeed. Try the formula below please.

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 ()
        )
    )

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-jiascu-msft · ‎06-13-2018

Hi Guido,

Try this formula please.

Column =
VAR lastestDate =
    CALCULATE (
        MAX ( Table1[date purchase] ),
        ALLEXCEPT ( Table1, Table1[Product group] )
    )
VAR PGHasPur =
    CALCULATETABLE (
        VALUES ( Table1[Product group] ),
        FILTER ( ALL ( 'Table1' ), Table1[date purchase] >= EDATE ( TODAY (), -4 ) )
    )
RETURN
    IF (
        ISBLANK ( [Amount] ),
        "No purchase",
        IF (
            NOT [Product group] IN PGHasPur
                && [date purchase] = lastestDate,
            "No purchase",
            BLANK ()
        )
    )

Identify_no_purchase_after_specific_date_or_no_purchase_at_all

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous · ‎06-13-2018

Hi @v-jiascu-msft

Thank you very much for your help.

The formula works almost perfect. However, I am using a merged table and therfore it could happen that an empty product group appears multimple times. In this case I would like to have a "no purchase" only once

I'll try to clarify with data:

Customer	Product group	product	date purchase	Amount	Price	No regular purchase
Customer A	A	AA	11-may-18	2	5
Customer A	A	AB	11-may-18	5	28
Customer A	B	BA	11-may-18	4	45
Customer A	B	BB	11-may-18	33	688
Customer A	B	BB	25-may-18	2	45
Customer A	C					No purchase
Customer A	D					No purchase
Customer A	D
Customer A	D
Customer A	E					No purchase
Customer A	E

I hope you can adjust the DAX so it only returns No Purchase once if there are multiple empty product groups under the same customer.

Regards,

Guido

v-jiascu-msft · ‎06-13-2018

Hi Guido,

I think you still consider the [Customer]. Please check out the formula below.

1. Add an index in the Query Editor due to all the blank columns are same.

2. New measure.

NEW =
VAR minIndex =
    CALCULATE (
        MIN ( Table1[Index] ),
        ALLEXCEPT ( Table1, Table1[Product group], Table1[Customer] )
    )
VAR lastestDate =
    CALCULATE (
        MAX ( Table1[date purchase] ),
        ALLEXCEPT ( Table1, Table1[Product group], Table1[Customer] )
    )
VAR PGHasPur =
    CALCULATETABLE (
        VALUES ( Table1[Product group] ),
        FILTER ( ALL ( 'Table1' ), Table1[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 ()
        )
    )

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous · ‎06-14-2018

Hi @v-jiascu-msft

Thank you very much again for your help. We are almost there 🙂

Becuase of the merged table it could happen that a product group appears multiple times. Sometimes there has been a purchase on a product group. However, because of the merged tables there could be a row where the same product group appears but is empty.

Try to explain in the data below (at the bottem A & B is added):

Customer	Product group	product	date purchase	Amount	Price	No regular purchase
Customer A	A	AA	11-may-18	2	5
Customer A	A	AB	11-may-18	5	28
Customer A	B	BA	11-may-18	4	45
Customer A	B	BB	11-may-18	33	688
Customer A	B	BB	25-may-18	2	45
Customer A	C					No purchase
Customer A	D					No purchase
Customer A	D
Customer A	D
Customer A	E					No purchase
Customer A	E
Customer A	A
Customer A	B

Hope you can help me with this.

Regards,

Guido

v-jiascu-msft · ‎06-14-2018

Hi Guido,

Let's follow the example in the first post. What should the column be like below?

Customer	Product group	product	date purchase	Amount	Price	No regular purchase
Customer A	A	AA	11-May-18	2	5
Customer A	A	AB	11-May-18	5	28
Customer A	B	BA	11-May-18	4	45
Customer A	B	BB	11-May-18	33	688
Customer A	B	BB	25-May-18	2	45
Customer A	C					No purchase
Customer A	D					No purchase
Customer B	A	AA	21-Apr-18	2	68
Customer B	A	AB	21-Apr-18	6	33
Customer B	A	AB	4-May-18	4	78
Customer B	A	AD	4-May-18	8	93
Customer B	B	BA	4-May-18	4	54
Customer B	B	BB	4-May-18	2	12
Customer B	C					No purchase
Customer B	D	DA	21-Dec-17	8	85
Customer B	D	DB	11-Jan-18	6	43	No purchase
Customer C	A					No purchase
Customer C	B	BA	6-Apr-18	4	93
Customer C	B	BB	11-Apr-18	5	5
Customer C	C	CA	24-May-16	6	72
Customer C	C	CB	6-Oct-16	4	47
Customer C	C	CC	21-Feb-17	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
Customer A	E
Customer A	E
Customer A	F
Customer A	F
Customer A	F

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous · ‎06-20-2018

Hi @v-jiascu-msft

Were you able to find a solution?

Regards,

Guido

v-jiascu-msft · ‎06-20-2018

Hi Guido,

Did you try the formula again? It seems working. Please check out the demo in the attachment.

NEW = 
VAR minIndex =
    CALCULATE (
        MIN ( Table1[Index] ),
        ALLEXCEPT ( Table1, Table1[Product group], Table1[Customer] )
    )
VAR lastestDate =
    CALCULATE (
        MAX ( Table1[date purchase] ),
        ALLEXCEPT ( Table1, Table1[Product group], Table1[Customer] )
    )
VAR PGHasPur =
    CALCULATETABLE (
        VALUES ( Table1[Product group] ),
        FILTER ( ALL ( 'Table1' ), Table1[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 ()
        )
    )

Identify no purchase after specific date or no purchase at all.JPG

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous · ‎06-20-2018

Hi @v-jiascu-msft

I tried it again and it indeed it works better.

After adapting it to my own data I discovered something else.

I changed all dates of Customer C to 2016

Customer	Product group	product	date purchase	Amount	Price	No regular purchase
Customer A	A	AA	11-May-18	2	5
Customer A	A	AB	11-May-18	5	28
Customer A	B	BA	11-May-18	4	45
Customer A	B	BB	11-May-18	33	688
Customer A	B	BB	25-May-18	2	45
Customer A	C					No purchase
Customer A	D					No purchase
Customer B	A	AA	21-apr-18	2	68
Customer B	A	AB	21-apr-18	6	33
Customer B	A	AB	4-May-18	4	78
Customer B	A	AD	4-May-18	8	93
Customer B	B	BA	4-May-18	4	54
Customer B	B	BB	4-May-18	2	12
Customer B	C					No purchase
Customer B	D	DA	21-dec-17	8	85
Customer B	D	DB	11-jan-18	6	43	No purchase
Customer C	A					No purchase
Customer C	B	BA	6-apr-16	4	93
Customer C	B	BB	11-apr-16	5	5	No Purchase
Customer C	C	CA	3-Oct-16	6	72
Customer C	C	CB	6-Oct-16	4	47
Customer C	C	CC	11-Oct-16	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

The No purchase marked in red is not appearing in the data

Regards,

Guido

v-jiascu-msft · ‎06-20-2018

We need to make a few changes indeed. Try the formula below please.

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 ()
        )
    )

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-jiascu-msft · ‎06-24-2018

Hi @Anonymous,

Did it work?

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous · ‎09-26-2018

Hi @v-jiascu-msft,

Found a new challenge.

Due to merging tables quarterly the following situation might accure:

If the next quarter starts customer A has no purchase yet. However the MIN index identifies it as "no purchase".

See example below:

Customer	Product group	product	date purchase	Amount	Price	No regular purchase
Customer A	A
Customer A	A
Customer A	A	AA	11-Aug-18	2	5
Customer A	A	AB	11-Aug-18	5	28
Customer A	B	BA	11-Aug-18	4	45
Customer A	B	BB	11-Aug-18	33	688
Customer A	B	BB	25-Aug-18	2	45
Customer A	C					No purchase
Customer A	D					No purchase
Customer B	A	AA	21-Jul-18	2	68
Customer B	A	AB	21-Jul-18	6	33
Customer B	A	AB	4-Aug-18	4	78
Customer B	A	AD	4-Aug-18	8	93
Customer B	B	BA	4-Aug-18	4	54
Customer B	B	BB	4-Aug-18	2	12
Customer B	C					No purchase
Customer B	D	DA	21-dec-17	8	85
Customer B	D	DB	11-jan-18	6	43	No purchase
Customer C	A					No purchase
Customer C	B	BA	6-apr-16	4	93
Customer C	B	BB	11-apr-16	5	5	No Purchase
Customer C	C	CA	3-Oct-16	6	72
Customer C	C	CB	6-Oct-16	4	47
Customer C	C	CC	11-Oct-16	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

Hope you can help me out.

regards,

Guido

Anonymous · ‎06-24-2018

Yes it worked! Thank you very much for your help.

Anonymous · ‎06-14-2018

@v-jiascu-msft

This it how it should look like:

Customer	Product group	product	date purchase	Amount	Price	No regular purchase
Customer A	A	AA	11-May-18	2	5
Customer A	A	AB	11-May-18	5	28
Customer A	B	BA	11-May-18	4	45
Customer A	B	BB	11-May-18	33	688
Customer A	B	BB	25-May-18	2	45
Customer A	C					No purchase
Customer A	D					No purchase
Customer B	A	AA	21-apr-18	2	68
Customer B	A	AB	21-apr-18	6	33
Customer B	A	AB	4-May-18	4	78
Customer B	A	AD	4-May-18	8	93
Customer B	B	BA	4-May-18	4	54
Customer B	B	BB	4-May-18	2	12
Customer B	C					No purchase
Customer B	D	DA	21-dec-17	8	85
Customer B	D	DB	11-jan-18	6	43	No purchase
Customer C	A					No purchase
Customer C	B	BA	6-apr-18	4	93
Customer C	B	BB	11-apr-18	5	5
Customer C	C	CA	24-May-16	6	72
Customer C	C	CB	6-Oct-16	4	47
Customer C	C	CC	21-feb-17	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

Regards,

Guido

Identify no purchase after specific date or no purchase at all

Helpful resources

Power BI Dataviz World Championships

Power BI Monthly Update - December 2025

FabCon Atlanta 2026

How to Get Your Question Answered Quickly

FabCon is coming to Atlanta

Identify no purchase after specific date or no purchase at all

Helpful resources

Power BI Dataviz World Championships

Power BI Monthly Update - December 2025

FabCon Atlanta 2026

How to Get Your Question Answered Quickly