cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
katjakok
Frequent Visitor

Propagation of customer level measure to the order level

I have an order table and based on the creation date of the order I am trying to calculate the count of new customer/returning customers and revenue created by both segment. Is it a bit more complicated because I want to have it dynamic based on the period that a user choses. (Attached is the power bi notebook with my failed attempts to calculate this :))

Here is the data example I use:

customerordercreated_at_dateamount
AA101.01.2060
AA210.01.2010
AA330.01.20200
BB123.09.1910
BB201.01.2020
BB310.01.2035
CC123.09.1910
CC230.01.2015


Desired result & explanation:
Let's say I want to see number of new/returning customers only for 01.01.2020. Based on the example above, customer A did its first order (A1) on 01.01.2020, hence customer A is a new customer for the given period (01.01.2020). Customer B did his first order (B1) on 23.09.19 and the second order (B2) was done on 01.01.202, hence, customer B is a returning customer for the given period. 
The revnue generated by new customer and returning customer for the period of 01.01.2020 is: 
new customer: 60 (A1) and returning customer: 10 (B2).

If I do the same calculations for the whole month of Janury for 2020, it will be like this:

 returning customernew customer
count2 (B & C)1 (A)
revenue70 (B2+B3 +C2)270 (A1+A2+A3)




My logic to calculate this was the following:
As a have a date slicer, I count number of orders before the latest date of the slicer and number of orders that were placed between the dates specified in the slicer. If those two numbers are equal, then it is a new customer. 

Here is the calculation for the new customer:

IsNewCustomer = 

VAR

EndDate = lastdate('Book1'[created_at_date])

VAR

StartDate = firstdate('Book1'[created_at_date])

VAR OrdersTillNow = CALCULATE (

DISTINCTCOUNT ( 'Book1'[order]),'Book1'[created_at_date]<=EndDate)

VAR OrdersInPeriod = CALCULATE (

DISTINCTCOUNT ( 'Book1'[order]),'Book1'[created_at_date]<=EndDate, 'Book1'[created_at_date]>=StartDate)

Return

CALCULATE (OrdersTillNow = OrdersInPeriod)

And then I tried to apply this to count new customers:

Count New Customers = CALCULATE(DISTINCTCOUNT(Book1[customer]), filter(Book1, [IsNewCustomer]))

And as you might already see, it doesn't work because the isNewCustomer is calculated on the customer level and not order level.

Screenshot 2020-12-18 at 22.19.59.png

Question:
How could I achieve the desired result that I provided in the example?


pbix notebook 

 

3 REPLIES 3
katjakok
Frequent Visitor

@Ashish_Mathur thank you for the notebook and the solution! Indeed, it does wotk for the small dataset.

However, if I have a big dataset and I want to display the number of new users over weeks/days, this solution doesn"t scale.

This is what I get from power bi desktop:

 

Screenshot 2020-12-22 at 11.25.04.png

Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

There is a tried and tested pattern for this. I have used it and it works. https://www.daxpatterns.com/new-and-returning-customers/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors