cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
oristides Regular Visitor
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
v-haibl-msft Super Contributor
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.

Old customers vs New customers. how to check that_1.jpg

 

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

Old customers vs New customers. how to check that_2.jpg

 

Best Regards,

Herbert

View solution in original post

10 REPLIES 10
kcantor Super Contributor
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.

There are two links.

 

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/



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




v-haibl-msft Super Contributor
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

oristides Regular Visitor
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?

kcantor Super Contributor
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.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




oristides Regular Visitor
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,

SergeiStPete Frequent Visitor
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

 

oristides Regular Visitor
Regular Visitor

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

Hello,

 

Thanks for your comment

 

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.

 

SergeiStPete Frequent Visitor
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.

v-haibl-msft Super Contributor
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.

Old customers vs New customers. how to check that_1.jpg

 

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

Old customers vs New customers. how to check that_2.jpg

 

Best Regards,

Herbert

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

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

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 219 members 2,272 guests
Please welcome our newest community members: