Forum Discussion

morab's avatar
morab
Advocate I
6 years ago
Solved

Repeat Customer

Hi, Good Day!   Following is the data table: OrderDate Distributor Code OutletCode SRName Category SKUCode OrderKG SalesKG 9/1/2019 14413 100033897 Shipon DDP 403 0.48 0.48 ...
Need Help
  • lc_finance's avatar
    lc_finance
    6 years ago

    Hi morab ,

     

     

    below is my proposed solution: a formula repeatCustomer that counts the repeat customer based on the chosen category. 

     

    RepeatCustomer = 
VAR customerTable = ADDCOLUMNS(VALUES('Table'[OutletCode]), 
"numberOfPurchases", CALCULATE(COUNTX('Table', [OutletCode])))

RETURN 
SUMX(customerTable, IF([numberOfPurchases]>1,1,0))

    And here is a screenshot:

     

     

    I hope this is what you were looking for.

     

    Regards,

     

    LC

    Interested in Power BI finance templates? Check out my blog at www.finance-bi.com

     

  • lc_finance's avatar
    lc_finance
    6 years ago

    Hi morab ,

     

    I believe I found the difference: in Excel, you are counting as 'repeat customers' only customers that buy on at least 2 different dates. The formula I proposed counts instead as 'repeat customers' any customer that has more than one transaction, even if the transaction is on the same date.

     

    As an example, outlet code 100032940 for category IFFO: this outlet bought 2 times both on September 26. This customer (2 transactions but on the same day) is not counted in the Excel, but is counted in Power BI.

     

     

    To have a count based on different days only, I had to add a calculated table to the model. Here is the formula for the table:

    Customer Table by Date = 
SUMMARIZE('Datatable','Datatable'[OutletCode],'Datatable'[OrderDate], 'Datatable'[Category])

    And here is the new measure, counting repeat customers based on different days:

    RepeatCustomerDifferentDays = 
VAR selectedCategory = SELECTEDVALUE('Datatable'[Category])

VAR customerTable = 
CALCULATETABLE (
ADDCOLUMNS(VALUES('Customer Table by Date'[OutletCode]), 
"numberOfPurchases", CALCULATE(COUNTX('Customer Table by Date', [OutletCode])))
, 'Customer Table by Date'[Category]= selectedCategory
)

RETURN 
SUMX(customerTable, IF([numberOfPurchases]>1,1,0))

    The result now matches the Excel:

     

    The result now matches the Excel.

     

    Here is the link for the PBI file: https://drive.google.com/open?id=1tjpQKf70VP4gqT-wrjWjL29j-SjlOtwI

     

    Enjoy!

     

    LC

    www.finance-bi.com

  • lc_finance's avatar
    lc_finance
    6 years ago

    Here is the new formula, I took the time to simplify it:

     

    RepeatCustomerDifferentDays New = 


SUMX(VALUES('Datatable'[OutletCode]),
    VAR dates =  CALCULATETABLE(VALUES('Datatable'[OrderDate]))
    RETURN IF(COUNTX(dates, [OrderDate])>1,1,0)

)
  • lc_finance's avatar
    lc_finance
    6 years ago

    Hi morab ,

     

     

    You can find my solution attached:

    https://finance-bi.com/wp-content/uploads/2020/01/customer-transactions-by-month.zip 

     

    Here is how it works:

    Outlet No = 

var numberOfMonths = SELECTEDVALUE('Number of Months'[number of month])
var outlets = VALUES('Table1'[OutletCode])
var purchaseByMonth = SELECTCOLUMNS(outlets,
"outlets", [OutletCode],
"months with purchase", 
    SUMX(VALUES('Calendar'[Year Month Number]), 
    var countTransactions = CALCULATE(COUNTROWS('Table1'))
    RETURN IF(countTransactions>0,1,0)
    )
)

var purchasedXMonths = COUNTX(FILTER(purchaseByMonth, [months with purchase]=numberOfMonths), [months with purchase])

RETURN purchasedXMonths

     

    The variable number OfMonths is equal to the number of month selected (for example: purchase in only 1 month, purchase in 2 months, etc)

    The variable outlets has a list of all the outlets

    The variable purchaseByMonth is a table with all the outlets and a column specifying on how many months the outlet bought the product

    Finally, the variable purchasedXMonth counts the number of outlets that bought for a specified number of months (based on the variable number of months).

    Does this help you?

     

    Regards

     

    LC

    Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

daedah's avatar
daedah
Helper II
6 years ago

yes please , I  share with you  Already

Thanks

lc_finance's avatar
lc_finance
Solution Sage
6 years ago

Hi daedah ,

 

 

You can download my proposed solution from here.

 

I updated the formula based on your data model. This is the DAX formula now:

Repeat customer from previous month = 

var datesInPreviousMonth = PREVIOUSMONTH('Sale_2017-2019'[BILL_DATE])
var customersCurrentMonth = VALUES('Sale_2017-2019'[CUST_ID])
var customersPreviousMonth = 
 CALCULATETABLE(
VALUES('Sale_2017-2019'[CUST_ID])
    , ALL('Sale_2017-2019'[BILL_DATE].[Month],'Sale_2017-2019'[BILL_DATE].[MonthNo],'Sale_2017-2019'[BILL_DATE].[Year])
    ,'Sale_2017-2019'[BILL_DATE] IN datesInPreviousMonth)

RETURN SUMX(customersCurrentMonth, IF([CUST_ID] IN customersPreviousMonth,1,0))

 

Also- I was not sure if you already had formulas for the other columns you wanted: new customer count, new customer sales, and percentage of new customer so I added those formulas for you.

You can find everything in the Power BI solution file.

 

Finally, this is what the report looks like for 2019:

 

I hope this helps you! Let me know if you have any question about it.

 

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

  • lc_finance's avatar
    lc_finance
    Solution Sage
    6 years ago

    Hi daedah ,

     

     

    you can download the new solution from here.

    As you mention, the new measure will count in October all repeat customers which were new in August or September.

     

    Here is the DAX code:

    Repeat customer from new customer 2 months ago = 

var customersCurrentMonth = VALUES('Sale_2017-2019'[CUST_ID])
var dates1MonthAgo = PREVIOUSMONTH('Sale_2017-2019'[BILL_DATE])
var dates2MonthsAgo = PREVIOUSMONTH(dates1MonthAgo)
var datesPast2months = UNION(dates1MonthAgo, dates2MonthsAgo)
var firstDay2MonthsAgo = FIRSTDATE(dates2MonthsAgo)
var customersPast2Months = 
 CALCULATETABLE(
VALUES('Sale_2017-2019'[CUST_ID])
    , ALL('Sale_2017-2019'[BILL_DATE].[Month],'Sale_2017-2019'[BILL_DATE].[MonthNo],'Sale_2017-2019'[BILL_DATE].[Year])
    ,'Sale_2017-2019'[BILL_DATE] IN datesPast2months)
var customersBefore2MonthsAgo = 
 CALCULATETABLE(
VALUES('Sale_2017-2019'[CUST_ID])
    , ALL('Sale_2017-2019'[BILL_DATE].[Month],'Sale_2017-2019'[BILL_DATE].[MonthNo],'Sale_2017-2019'[BILL_DATE].[Year])
    ,'Sale_2017-2019'[BILL_DATE]<firstDay2MonthsAgo)
var newCustomersPast2Months = EXCEPT(customersPast2Months, customersBefore2MonthsAgo )


RETURN SUMX(customersCurrentMonth, IF( [CUST_ID] IN newCustomersPast2Months,1,0))

     

    Here is how it works:

    - customersCurrentMonth -> these are the customers in October

    - dates1monthAgo -> these are the dates for September

    - dates2monthsAgo -> these are the dates for August

    - datesPast2months -> these are the dates of August and September

    - firstDay2MonthsAgo -> this is the first of August

    - customersPast2Months -> these are the customers in August and September

    - customersBefore2MonthsAgo -> these are the customer that bought before August

    - newCustomersPast2Months -> these are the new customers in August and September

    The final SUMX counts all the repeat customers in October, which were new customers in August and September.

     

    Does this help you?

     

    LC

    Interested in Power BI and DAX? Check out my blog at www.finance-bi.com

     

  • daedah's avatar
    daedah
    Helper II
    6 years ago

    It Great  the Data it Correct

     

    Thank you very much 💙🙏💙

  • daedah's avatar
    daedah
    Helper II
    6 years ago

    Best Regards,

  • daedah's avatar
    daedah
    Helper II
    6 years ago

    Thank you very much for your kindly provided this data  to us in my view point , a definition of "a new customer refers to being a new customer starts from the first date of register" not related to time flies  I got the formula already 🙏

     

    I wish to Ark another Question , 

    I Would like about the data of new customer since 3 month ago, the new customer who bought the company' product in August 

    Did the back to buy again in the 2 month later?

    As following the picture

    example link for raw data and report format require

    https://drive.google.com/file/d/1UzeeB14hWkPPSdXfqVIQJbosFekPkq78/view?usp=sharing 

     

    Thank you very much for help full 🙏

  • daedah's avatar
    daedah
    Helper II
    6 years ago

    Thank you very much for your help 🙏🙏

  • daedah's avatar
    daedah
    Helper II
    6 years ago

    excuse me please 

    I trying calculate dax formula for districtcount number for customer 3 month ago As below formula

    districtcount 3 month ago =
    var customersCurrentMonth = VALUES('Sale_2017-2019'[CUST_ID])
    var dates1MonthAgo = PREVIOUSMONTH('Sale_2017-2019'[BILL_DATE])
    var dates2MonthsAgo = PREVIOUSMONTH(dates1MonthAgo)
    var dates3Monthsago = PREVIOUSMONTH(dates2MonthsAgo)
    var datesPast3months = UNION(dates1MonthAgo, dates2MonthsAgo,dates3Monthsago)
    var customersPast3months = CALCULATETABLE(
    VALUES('Sale_2017-2019'[CUST_ID])
    , ALL('Sale_2017-2019'[BILL_DATE].[Month],'Sale_2017-2019'[BILL_DATE].[MonthNo],'Sale_2017-2019'[BILL_DATE].[Year])
    ,'Sale_2017-2019'[BILL_DATE] IN datesPast3months)

    RETURN SUMX(customersCurrentMonth, IF(
    [CUST_ID] IN customersPast3months
    ,1,0))

    but value it mistake  I want to recommend ,How wrong this for dax formula?

    Thank you very much

    Raw data Link:
    https://drive.google.com/file/d/1MhAXgUodUnxXHydyxSAxk91QJgqLbIQ6/view?usp=sharing

  • lc_finance's avatar
    lc_finance
    Solution Sage
    6 years ago

    Hi daedah ,

     

     

    could you share the Power BI file where you saw the wrong value and the value you expect to see?

     

    Based on that, I will be happy to help you

     

    LC

     

  • lc_finance's avatar
    lc_finance
    Solution Sage
    6 years ago

    HI daedah ,

     

    I could not load your raw data. It looks like the date is for the year 2062.

     

    Can you share the Power BI file you have with the raw data included?

     

    LC 

     

  • lc_finance's avatar
    lc_finance
    Solution Sage
    6 years ago

    Hi daedah ,

     

     

    I verified and I confirm that the 'number of customers 3 months ago' in September are indeed 459.

    I added a table to check this to the Power BI, I called this table customer check. You can download it from here.

     

     

    With this table, it is easy to see the customers who bought in September, august (month - 1), July (month -2) and June (month -3).

    In the end, I checked the customers buying in June plus the past 3 months and I created a sum.

     

    Does this help you?

     

    LC

    Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

     

     

  • daedah's avatar
    daedah
    Helper II
    6 years ago

    Hi, lc_finance

    I tring to calculate DAX measure for power bi ,calculate repeat maintenance by machine_name and Reason each  month

     

    I Use formula

    repeat maintenance =
    var datesInMonth = VALUES('Date'[Date])
    var CurrentMonth = VALUES(Maintenance[plate_id])
    var MaintenanceInMonth =
    CALCULATETABLE(
    VALUES(Maintenance[plate_id])
    , All (Maintenance[repair]
    ,'Date'[Date] IN datesInMonth)
    RETURN SUMX(CurrentMonth, IF([plate_id] IN MaintenanceInMonth,1,0))
     
    But someting went wrong

     

    Example data Table Name : Maintenance

     

    Date                          plate_id                              repair
    1/11/2019                 A                                         Person

    2/11/2019                 B                                         spare part

    3/11/2019                 A                                         Person

    10/11/2019               C                                         Using

    12/11/2019               B                                         spare part

    1/12/2019                 A                                         person

     

    Result of November
    Machine_Name                     count of maintenance by month

    A                                            2

    B                                            2

    C                                           1

     

    Thank you for you help