cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Old customers vs New customers. how to check that?? Help

I want to check 2 scenarios

1) I want to compare sales for the customers with less than 1 year old vs clients for more than 1 year old in a period of time X.

2) I want to compare total sales for the clients converted teh LAST YEAR vs the Customers converted THIS YEAR in a period of time X.

I have Converted Date of the customers. Just I don know to performance a Measure our a Running(by time) Column.

Thanks,

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Old customers vs New customers. how to check that?? Help

@oristides

I assume you have a dataset likes below. We can create two measures to get the sales of old and new customers.

For example, B is created on 3/1/2014, then in 2015, old customers are A and B, new customers are C and D.

```Sales_OldCustomer =
CALCULATE (
SUM ( Sales[Amount in USD] ),
FILTER ( Sales, YEAR ( Sales[Date] ) > RELATED ( Customer[Created Year] ) )
)```
```Sales_NewCustomer =
CALCULATE (
SUM ( Sales[Amount in USD] ),
FILTER ( Sales, YEAR ( Sales[Date] ) = RELATED ( Customer[Created Year] ) )
)```

Best Regards,

Herbert

10 REPLIES 10
Super Contributor

## Re: Old customers vs New customers. how to check that?? Help

I believe this blog post addresses your question @oristides. This method worked for me.

http://www.powerpivotpro.com/2013/01/new-customers-per-day-generalized-to-new-customers-per-month-et...

http://www.powerpivotpro.com/2014/12/repeat-customers-in-dax-three-flavors/

Proud to be a Datanaut!

Super Contributor

## Re: Old customers vs New customers. how to check that?? Help

@oristides

You can create a calendar table and use time intelligence functions like SAMEPERIODLASTYEAR, DATESBETWEEN.

If the blog provided by @kcantor cannot address your question, you’d better provide some sample data to us.

Best Regards,

Herbert

Regular Visitor

## Re: Old customers vs New customers. how to check that?? Help

I tried to use dates between... a table calendar and parraller period but i Cant get it.

I want to get for example a bar chart for sales in quarters divided by 2 labels. New vs olds in a quarter.. but next quarter maybe some of the customers aren't NEW anymore.

How is the Dax functions for columns or measures to develop that?

Super Contributor

## Re: Old customers vs New customers. how to check that?? Help

@oristides

The Dax you need is found in the blog postings I shared... at least examples are. In order to be specific and create those dax expression tailored to your data, we would need some sample data to work with that gives us an idea of what you have, what it is called, and where it lives.

Proud to be a Datanaut!

Regular Visitor

## Re: Old customers vs New customers. how to check that?? Help

I Have:

1) Sales table, with customer key, Amount in USD, date etc.

2) Customer Table, With Created Date field.

3) calendar table, linked with date in Sales table

I want to se bars by years. grouped by New customers and Old Customers. New customers are customers that has 1 year sinces a Created Date value.

I WANT this:

Year                           2012           2013               2014            2015

NewCustomers         \$2500         \$500               \$1350           \$600

oldCustomers           \$1200         \$2100             \$ 900             \$1000

TOTAl Sales             \$3700         \$2600             \$2250           \$1600

In the Customer Table. I tried to create a feature that is new and Old. BUT is fixed fjust for the last year, And the rest customers are old. I used a columns called is new: IsNew=  today()< CreatedDate+365. ( I confess is a big mistake)

So I got a table that works only for the last year.

Year                           2012           2013               2014            2015

NewCustomers         \$0               \$0                   \$0                 \$600

oldCustomers           \$3700         \$2600             \$2250           \$1000

TOTAl Sales             \$3700         \$2600             \$2250           \$1600

What is not true because in 2013 I got new accounts and old accounts. but now this customers will contribute to the OLD customers because is old now is being. Example: customer A was new in 2013 but now is OLD. So sales in 2013 goes to NEWcustomers and sales later goes to OldCustomers.

Thanks,

Frequent Visitor

## Re: Old customers vs New customers. how to check that?? Help

Small comment, if you use Time Intelligince functions you have to mark Calendar as date table. Power BI Desktop doesn't support this so far, work arounds are here Time Intelligence in Power BI Desktop.

And more patterns you may find at New and-Returning Customers

Regular Visitor

## Re: Old customers vs New customers. how to check that?? Help

Hello,

1) Calendart is table is a Date table. With year, quarters, date, months, month name. etc.

2) The blogs didnt' help me. I saw before. I really couldn figure out how to perform that.

Frequent Visitor

## Re: Old customers vs New customers. how to check that?? Help

Yes, Calendar table is the Date table wich covers all time periods you have.

Let asume you have dimSales table joined many-to-one to dimCalendar. And dimCustomers table which is one-to-many to Sales.

Under Sales table you have two measures:

Sales=SUM(dimSales[InvoicedSum]) and

Customers=DISTINCTCOUNT(dimSales[Customer ID])

On the top you may add

Sales by New Customers=CALCULATE([Sales],

FILTER(ALL(dimSales),

RELATED(dimCustomers[Customer Joined Date] <= MAX(dimCalendar[Date]) &&

RELATED(dimCustomers[Customer Joined Date] >= MIN(dimCalendar[Date)

)

)

Exactly the same for New Customers measure, just use in above CALCULATE([Customers],....

And simular for Old Customers.

Please note i didn't test that and that's if i correctly understood your logic - you sort old/new customers based on some date you keep in Customers table. Not on the date of first invoice. If the latest the logic will be more complicated.

Super Contributor

## Re: Old customers vs New customers. how to check that?? Help

@oristides

I assume you have a dataset likes below. We can create two measures to get the sales of old and new customers.

For example, B is created on 3/1/2014, then in 2015, old customers are A and B, new customers are C and D.

```Sales_OldCustomer =
CALCULATE (
SUM ( Sales[Amount in USD] ),
FILTER ( Sales, YEAR ( Sales[Date] ) > RELATED ( Customer[Created Year] ) )
)```
```Sales_NewCustomer =
CALCULATE (
SUM ( Sales[Amount in USD] ),
FILTER ( Sales, YEAR ( Sales[Date] ) = RELATED ( Customer[Created Year] ) )
)```

Best Regards,

Herbert

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and 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.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 65 members 1,227 guests
Recent signins: