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

new customers

Hello -

 

I am trying to calculate a New Customer Analysis.  I have 3 years worth of data and I am trying to determine which customers are new in 2017 (no transactions in 2015-2016).

 

I have a Customers Table (joined on Cust_ID) and a Date Table (Joined on Transaction Date) that are both joined to my Transaction table and below are a few sample transactions in my data.

 

Cust_IDTransaction_DateAmount
11/1/2015   100.00
25/1/2015   100.00
35/1/2016   100.00
410/1/2016   100.00
11/1/2017   100.00
45/1/2017   100.00
57/1/2017   100.00
68/1/2017   100.00
79/1/2017   100.00

 

So in this example I am expecting to see 2017 New Customers = 3 for a total Amount of $300.

 

Is there a measure that I can use in Order to pull this information?

 

Thanks
Ryan

1 ACCEPTED SOLUTION

Accepted Solutions
Fitin1rb Regular Visitor
Regular Visitor

Re: new customers

@Zubair_Muhammad the work around that I am using is to set a Page Filter for the Cust_ID from the Calculated Table and include all Cust_ID <> blank and this seems to solve my issue for the time being.

13 REPLIES 13
jthomson New Contributor
New Contributor

Re: new customers

The approach I'd take is to look to use the MIN function and return the earliest date from the transaction date, and use that as a way to filter your data table. I'm not sure whether you could use this all in measures or whether you'd have to make a calculated column in your customer table

Super User
Super User

Re: new customers

Hi @Fitin1rb

 

One way of doing it

 

Go to Modelling tab>>>select the "New Table" button

 

New Customers Table =
VAR customersIn2017 =
    CALCULATETABLE (
        VALUES ( Transactions_Table[Cust_ID] ),
        YEAR ( Transactions_Table[Transaction_Date] ) = 2017
    )
VAR customersbefore2017 =
    CALCULATETABLE (
        VALUES ( Transactions_Table[Cust_ID] ),
        YEAR ( Transactions_Table[Transaction_Date] ) <> 2017
    )
RETURN
    SUMMARIZE (
        EXCEPT ( customersIn2017, customersbefore2017 ),
        Transactions_Table[Cust_ID],
        "Sales", CALCULATE (
            SUM ( Transactions_Table[Amount] ),
            YEAR ( Transactions_Table[Transaction_Date] ) = 2017
        )
    )

15.png

Super User
Super User

Re: new customers

Hi @Fitin1rb

 

Another way of doing it is to Add a Calculated Column in the Transactions Table identifying customers as Old or New

 

New or Old =
IF (
    YEAR ( Transactions_Table[Transaction_Date] ) = 2017,
    IF (
        CALCULATE (
            COUNTROWS ( Transactions_Table ),
            FILTER (
                ALLEXCEPT ( Transactions_Table, Transactions_Table[Cust_ID] ),
                YEAR ( Transactions_Table[Transaction_Date] ) < 2017
            )
        )
            >= 1,
        "Old",
        "New"
    )
)

 

16.png

 

Fitin1rb Regular Visitor
Regular Visitor

Re: new customers

@Zubair_Muhammad thanks for the reply.  Creating a table worked to get the #'s I was looking for, however, when I try to join it to my transaction table, so that I can see what customer name, industries, city/state etc our new business came from in 2017, it has every single value the same across the board unless I keep Cust_ID in there.  Is there anyway to change that?

 

As for the other suggestions, my transaction data set is well over 1 million rows of data, would creating a calculated column like that on that large of a data set cause any performance issues?

 

Thanks
Ryan

Super User
Super User

Re: new customers

Hi @Fitin1rb

 

Were you able to create a relationship between CALCULATED TABLE (of new customers) and Customers Information Table?

 

If relationship is established, it should be easy to view more information about these new customers

Super User
Super User

Re: new customers

@Fitin1rb

 

As a Measure, try this

 

New or Old_MEASURE =
IF (
    YEAR ( SELECTEDVALUE ( Transactions_Table[Transaction_Date] ) ) = 2017,
    IF (
        CALCULATE (
            COUNTROWS ( Transactions_Table ),
            FILTER (
                ALLEXCEPT ( Transactions_Table, Transactions_Table[Cust_ID] ),
                YEAR ( Transactions_Table[Transaction_Date] ) < 2017
            )
        )
            >= 1,
        "Old",
        "New"
    )
)

18.png

Fitin1rb Regular Visitor
Regular Visitor

Re: new customers

Hi @Zubair_Muhammad I have created a relationship between the Calculated Table (New Customers) and the Customer Info Dimensions table (which is joined to my Transaction Table by Cust_ID) on the Cust_ID.  This is the type of results that I am seeing.

Power Bi.png

Super User
Super User

Re: new customers

@Fitin1rb

 

Could you share the File?

 

A small question?

The sales field in the above Table Visual... is it coming from Transactions Table or the Calculated Table?

Fitin1rb Regular Visitor
Regular Visitor

Re: new customers

@Zubair_Muhammad Unfortunately I cannot share the file.  I am using the SALES amount from the calculated table of new customers.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 278 members 2,894 guests
Please welcome our newest community members: