Count of orders for Return and New Customers

[ManjunathaEP]

Hi, Thank you in advance for looking into my sample data and providing a DAX for it

I have simple sample data as mentioned below and I would like to calculate the count of orders for repeat customers

Customer	Order No	Year	Amount
ABC	1	2019	10
XYZ	2	2019	20
YRP	3	2019	34
HM	4	2019	56
XYZ	11	2020	67
ABC	22	2020	44
YRP	33	2020	46
HYD	44	2020	66
ABC	55	2020	77
XYZ	66	2020	67

 From the above table there are 3 customers (XYZ, ABC and YRP) are returned in 2020 compared to 2019. 

I would like to calculate the count of the orders for the above repeat customers in 2020 and the count of distinct orders=5

Please help me by providing suitable DAX for it

Regards,

Manjunatha EP

[daxer-almighty]

// My understanding of the task:
// Need to find out which customers
// bought something in the same
// period last year and then calculate
// the number of orders in the current
// period for these customers.
// To do the above correctly you have to
// have a Calendar table in the model.
// One-table model is NOT ENOUGH. Also,
// you should have a dimension called
// Customers that will hold all the customers
// in your model. Assuming the above is
// in place, you can write:

[# Orders] = DISTINCTCOUNT( Sales[Order No] )

[# Repeat Customer Orders] =
var CustomersWhoBoughtLY =
    CALCULATETABLE(
        // This calculation is correct on condition
        // that an entry in the fact table, call it
        // Sales, means a customer has bought something.
        // If this is not the case, you'll have to
        // adjust this condition.
        DISTINCT( Sales[CustomerID] ),
        // Please bear in mind that Calendar must 
        // be marked as a date table in the model
        // for this time-intel function to work OK.
        SAMEPERIODLASTYEAR( 'Calendar'[Date] )
    )
var Result =
    CALCULATE(
        [# Orders],
        KEEPFILTERS( CustomersWhoBoughtLY )
    )
return
    Result

[ManjunathaEP]

Thank you for providing DAX, I have given the sample table. Actually, repeat customers or return customer means, a customer returned back and purchased a product. It means, If a customer purchased in 2018 and the same customer purchases in 2020, I need to treat that customer as Return/repeat customers. Then, I have to calculate the count of the orders of that customer in 2020.

In the given table, please assume one of the customers (PS) was placed an order in 2018 and then returned in 2020, I have to considered as return/repeat customer in 2020 and need to calculate the count of orders for the year 2020.

[daxer-almighty · Accepted solution]

[# Repeat Customer Orders] =
var FirstDateOfCurrentPeriod = MIN( 'Calendar'[Date] )
var CustomersWhoBoughtBefore =
    CALCULATETABLE(
        // This calculation is correct on condition
        // that an entry in the fact table, call it
        // Sales, means a customer has bought something.
        // If this is not the case, you'll have to
        // adjust this condition.
        DISTINCT( Sales[CustomerID] ),
        // Please bear in mind that Calendar must 
        // be marked as a date table in the model
        // for this time-intel function to work OK.
        'Calendar'[Date] < FirstDateOfCurrentPeriod
    )
var Result =
    CALCULATE(
        [# Orders],
        KEEPFILTERS( CustomersWhoBoughtBefore )
    )
return
    Result

The only change is to the period that you want to consider "prior." This time the prior period is everything from the beginning of time to the very last date before the start of the current period. All the conditions I named before must hold for this to work correctly.