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

[oristides]

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, 

[v-haibl-msft]

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

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]

@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.

[oristides]

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]

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]

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.

[v-haibl-msft · Accepted solution]

@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

[oristides]

Wow Amazing!!!  Very usefull entry, i will help a lot of people!! ..  and yes you supposed well how data was modelled. Thank you very very much!! tested and work right!!

[SergeiStPete]

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.

[kcantor]

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/