How to filter new customer purchase in 2020 and not purchase in 2019 ?

[Paulyeo11]

Hi All

I need to expression to filter those customer purchase only in 2020 and not 2019 , as indicated in below image , those i highlight in red box :-

My PBI file :-

https://www.dropbox.com/s/4wembkpd1ge06vj/PBT_V01015%20Buy%20in%202020%20and%20not%20buy%20in%202019...

Paul

[AlB · Accepted solution]

Hi @Paulyeo11 

See it all at work in the attached file. Create this measure and place it in the visual:

SALES_ V2 = 
VAR sales2019_ = CALCULATE(SUM(SALES[sales]), 'Date'[Year] = 2019)
VAR sales2020_ = CALCULATE(SUM(SALES[sales]), 'Date'[Year] = 2020)
RETURN
    IF (
        NOT ISBLANK ( sales2019_ ) && ISBLANK ( sales2020_ )
            && ( SELECTEDVALUE ( 'Date'[Year] ) = 2019 || NOT ISFILTERED ( 'Date'[Year] )),
        sales2019_
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@Paulyeo11 

Sure. Minor modification to your code:

2020_only V2 =
VAR sales2018_ =
    CALCULATE ( SUM ( SALES[sales] ), 'Date'[Year] = 2018 )
VAR sales2019_ =
    CALCULATE ( SUM ( SALES[sales] ), 'Date'[Year] = 2019 )
VAR sales2020_ =
    CALCULATE ( SUM ( SALES[sales] ), 'Date'[Year] = 2020 )
RETURN
    IF (
        NOT ISBLANK ( sales2020_ ) && ISBLANK ( sales2019_ )
            && ISBLANK ( sales2018_ )
            && (
                SELECTEDVALUE ( 'Date'[Year] ) = 2020
                    || NOT ISFILTERED ( 'Date'[Year] )
            ),
        sales2020_
    )

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Paulyeo11]

Hi AIB

Appreciated very much , you code working fine. Thank you very much.

Paul

[AlB · Accepted solution]

Hi @Paulyeo11 

See it all at work in the attached file. Create this measure and place it in the visual:

SALES_ V2 = 
VAR sales2019_ = CALCULATE(SUM(SALES[sales]), 'Date'[Year] = 2019)
VAR sales2020_ = CALCULATE(SUM(SALES[sales]), 'Date'[Year] = 2020)
RETURN
    IF (
        NOT ISBLANK ( sales2019_ ) && ISBLANK ( sales2020_ )
            && ( SELECTEDVALUE ( 'Date'[Year] ) = 2019 || NOT ISFILTERED ( 'Date'[Year] )),
        sales2019_
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Paulyeo11]

Hi AIB

Below expression is filter those new customer buy in 2020. Meaning those who buy in 2020 and not buy in 2019.

2020_only =

VAR sales2019_ = CALCULATE(SUM(SALES[sales]), 'Date'[Year] = 2019)

VAR sales2020_ = CALCULATE(SUM(SALES[sales]), 'Date'[Year] = 2020)

RETURN

IF(NOT ISBLANK(sales2020_) && ISBLANK(sales2019_) && (SELECTEDVALUE('Date'[Year]) = 2020 || NOT ISFILTERED('Date'[Year])), sales2020_)

May i know how to add one more condition to above expression :-

Include those customer Buy in 2020 and also not buy in 2018. And consider as 2020 new customer. ( Because i have 3 year data )

https://www.dropbox.com/s/erfb7vqvoro2igg/How%20to%20filter%20new%20customer%20purchase%20in%202020%...

Remark :-

Pls note that above sample data only have 2 year data , my actual data have 3 year data .

Paul Yeo

[amitchandak]

@Paulyeo11 , I think Nutek PTE LTD will also not come. Please find the attached file after signature