cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wenners1968
Regular Visitor

Customers who bought last year but not this year

Hi,

 

I'm trying to find the best way of meassuring new, lost and returning customers from a dataset.

The data is in this format:

Data.PNG

 

So basically I want to be able to see:

who was in the 2017 Edition but not in the 2018 Edition (lost)

who is in both Editions (returning)

who is in the 2018 Edition but not in the 2017 Edition (new)

 

What is the best way of doing this?

 

 

1 ACCEPTED SOLUTION
v-sihou-msft
Microsoft
Microsoft

@wenners1968

 

In this scenario, you should use a Year column. Then you need to summarize the previous year amount and current year amount to count each kind of customers. 

 

[New Customers] :=
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Table[Customer] ),
            "PreviousPurchase", CALCULATE (
                COUNTROWS ( Table ),
                FILTER ( ALL ( 'Table' ), Table[Year] = MIN ( Table[Year] ) - 1 )
            ),
            "CurrentPurchase", CALCULATE (
                COUNTROWS ( Table ),
                FILTER ( ALL ( 'Table' ), Table[Year] = MIN ( Table[Year] ) )
            )
        ),
        [PreviousPurchase] = 0
            && [CurrentPurchase] <> 0
    )
)
[Lost Customers] :=
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Table[Customer] ),
            "PreviousPurchase", CALCULATE (
                COUNTROWS ( Table ),
                FILTER ( ALL ( 'Table' ), Table[Year] = MIN ( Table[Year] ) - 1 )
            ),
            "CurrentPurchase", CALCULATE (
                COUNTROWS ( Table ),
                FILTER ( ALL ( 'Table' ), Table[Year] = MIN ( Table[Year] ) )
            )
        ),
        [PreviousPurchase] <> 0
            && [CurrentPurchase] = 0
    )
)
[Returning Customers] :=
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Table[Customer] ),
            "PreviousPurchase", CALCULATE (
                COUNTROWS ( Table ),
                FILTER ( ALL ( 'Table' ), Table[Year] = MIN ( Table[Year] ) - 1 )
            ),
            "CurrentPurchase", CALCULATE (
                COUNTROWS ( Table ),
                FILTER ( ALL ( 'Table' ), Table[Year] = MIN ( Table[Year] ) )
            )
        ),
        [PreviousPurchase] <> 0
            && [CurrentPurchase] <> 0
    )
)

For more details, please refer to a good article below:

 

http://www.daxpatterns.com/new-and-returning-customers/

 

Regards,

View solution in original post

3 REPLIES 3
v-sihou-msft
Microsoft
Microsoft

@wenners1968

 

In this scenario, you should use a Year column. Then you need to summarize the previous year amount and current year amount to count each kind of customers. 

 

[New Customers] :=
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Table[Customer] ),
            "PreviousPurchase", CALCULATE (
                COUNTROWS ( Table ),
                FILTER ( ALL ( 'Table' ), Table[Year] = MIN ( Table[Year] ) - 1 )
            ),
            "CurrentPurchase", CALCULATE (
                COUNTROWS ( Table ),
                FILTER ( ALL ( 'Table' ), Table[Year] = MIN ( Table[Year] ) )
            )
        ),
        [PreviousPurchase] = 0
            && [CurrentPurchase] <> 0
    )
)
[Lost Customers] :=
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Table[Customer] ),
            "PreviousPurchase", CALCULATE (
                COUNTROWS ( Table ),
                FILTER ( ALL ( 'Table' ), Table[Year] = MIN ( Table[Year] ) - 1 )
            ),
            "CurrentPurchase", CALCULATE (
                COUNTROWS ( Table ),
                FILTER ( ALL ( 'Table' ), Table[Year] = MIN ( Table[Year] ) )
            )
        ),
        [PreviousPurchase] <> 0
            && [CurrentPurchase] = 0
    )
)
[Returning Customers] :=
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Table[Customer] ),
            "PreviousPurchase", CALCULATE (
                COUNTROWS ( Table ),
                FILTER ( ALL ( 'Table' ), Table[Year] = MIN ( Table[Year] ) - 1 )
            ),
            "CurrentPurchase", CALCULATE (
                COUNTROWS ( Table ),
                FILTER ( ALL ( 'Table' ), Table[Year] = MIN ( Table[Year] ) )
            )
        ),
        [PreviousPurchase] <> 0
            && [CurrentPurchase] <> 0
    )
)

For more details, please refer to a good article below:

 

http://www.daxpatterns.com/new-and-returning-customers/

 

Regards,

View solution in original post

miltenburger
Helper V
Helper V

Why don't you just use a filter?

Filter on 2017 edition,

Filter on 2018 edition,

Filter on both editions.

 

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380820?tab=Overview

Or

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380859

I need to show it as a total figure on a card.

As in: total renewals for 2018 Edition.

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors