cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

4 REPLIES 4
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,

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.

 

Hi All

 

Apologies for reopening this thread but I'm stuck as how to implement the above solution in my report.

 

I have a Sales FACT table as below:

 

Cust1.jpg

 

The report has a Slicer with the FiscalYear from the Dates table.  This field coresponds with AccYear in the FACT table.

 

Am looking as above to show New Customers and Lost Customers both in number and value.

 

So far I've only tried to get the number of New Customers to work but am failing.

 

The measure I've created is:

 

 

New Customers 2 = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Sales[CustID] ),
            "@PreviousPurchase", CALCULATE (
                COUNTROWS ( Sales ),
               FILTER ( ALL ( 'Sales' ), Sales[AccYear] = SELECTEDVALUE(Dates[FiscalYear])-1 )
                           ),
         "@CurrentPurchase", CALCULATE (
                COUNTROWS ( Sales ),
                FILTER ( ALL ( 'Sales' ), Sales[AccYear] = SELECTEDVALUE(Dates[FiscalYear]) )
            )                  
        ),          
    
    [@PreviousPurchase] = 0 && [@CurrentPurchase]<>0
)
)

 

 
The result of the above for FiscalYear 2021 (or any Fiscal Year chosen via slicer) is zero even with hard-coding the FiscalYears in.
 
If I just count rows of distinct customers: in 2020 there are 28 and in 2021 there are 22 of which there are 3 new customers in 2022.
 
Any thoughts on what is happening here?
 
Many thanks 
 
Jake 

 

 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors