cancel
Showing results for
Did you mean:
Highlighted
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_ID Transaction_Date Amount 1 1/1/2015 100.00 2 5/1/2015 100.00 3 5/1/2016 100.00 4 10/1/2016 100.00 1 1/1/2017 100.00 4 5/1/2017 100.00 5 7/1/2017 100.00 6 8/1/2017 100.00 7 9/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
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
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

## Re: new customers

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

Super User

## Re: new customers

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"
)
)```

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

## Re: new customers

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

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"
)
)```

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.

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?

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.

Announcements

#### 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

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 278 members 2,894 guests
Recent signins: