New, Retentive and Returning Customers

[RoswithaR]

Hi to everyone,

I have calculated flags for new, retentive and returning customers (as seen on the screenshot in the first/left visual). 

In the second table I would like to sum new, retentive and returning customers within the month, as it is done for customers and orders.

For example, in March 2025 should be 3 new and 2 returning customers, in October 2021 - 2 returning and 1 retentive customer, etc.

Most of my tries led to error or the sums were not displayed.

I would appreciate any hint/advice, how to calculate it.

I calculated e.g. Returning Customers as follows:

ReturningCust =

VAR CurrMon = MAX(Test_Datenset[OrderDate])

RETURN

    CALCULATE(

        DISTINCTCOUNT(Test_Datenset[CustID]),

        FILTER(

            VALUES(Test_Datenset[CustID]),

            CALCULATE(  --Order(s) more than 24 months ago

                COUNT(Test_Datenset[OrderID]),

                'Test_Datenset'[OrderDate] < EDATE(CurrMon, -24)

            ) > 0 &&

            CALCULATE(  --No orders in the last 24 months

                COUNT(Test_Datenset[OrderID]),

                Test_Datenset[OrderDate] >= EDATE(CurrMon, -24),

                Test_Datenset[OrderDate] < EOMONTH(CurrMon, -1)

            ) = 0 &&

            CALCULATE(  --Order(s) in the current month

                COUNT(Test_Datenset[OrderID]),

                'Test_Datenset'[OrderDate] > EOMONTH(CurrMon, -1)

            ) > 0

        )

    )

Thanks in advance

Roswitha

p.s. I have prepared a pbix file, if someone is willing to look at it.

[speedramps]

Hi @RoswithaR 

New, lost,  retentive and returning customers is a common requirement.

It is best prartice to use DAX measure, rather than calculatre a column for each order.

Learn about them here

https://www.daxpatterns.com/new-and-returning-customers/

If you take take time to learn and build these measures , then you will use them always in your all reports.

They are much better than adding columns to each and every table 

Please clcim thumbs up and acceopt solution. Thank you

NO new customers = 
/* DOCUMENTATION
Get number of new customers as follows:-
Use addcolumns to get a set with (CustomerKey,Previous Rows)
Then filters the set to just to include Rustomereys with no previous rows.
Then count the number of Customers
*/

VAR mindate =  MIN ( 'Calendar'[Date] ) 

VAR NewCustomers = 
    FILTER (
        ADDCOLUMNS (
            VALUES ( Sales[CustomerKey] ),
            "PreviousRows", 
                CALCULATE (COUNTROWS (Sales),
                FILTER (ALL ( 'Calendar'[Date] ),'Calendar'[Date] < mindate))
        ),
        [PreviousRows] = 0
    )

RETURN
COUNTROWS(NewCustomers) 

NO returning customers = 
/* DOCUMENTATION *
Number of returning customers 
*/
VAR mindate = MIN('Calendar'[Date])

RETURN
COUNTROWS (
    CALCULATETABLE (
        VALUES ( Sales[CustomerKey] ),
        VALUES ( Sales[CustomerKey] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] < mindate 
        )
    )
)

NO lost customers = 
/* DOCUMENTATION *
Number of lost customers
*/
VAR maxdate = MAX('Calendar'[Date])

VAR currentcustomers =
    VALUES ( Sales[CustomerKey])
  
VAR futurerecustomers =
    CALCULATETABLE (
        VALUES ( Sales[CustomerKey]),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] > maxdate 
        )
    )

VAR lostscustoemrs =
    EXCEPT(currentcustomers,futurecustomers)
    
RETURN
COUNTROWS(lostcustomers)

I want to help you but your description is too vague. Please write it again.

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want. (That is just crazy). ‌‌
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

[v-csrikanth]

Hi @RoswithaR 
Thanks for confirming you will be looking into the solution later.

As per Microsoft community guidelines, we will now proceed to close this thread to keep discussions focused and manageable. If you still need assistance, you're welcome to start a new thread in the community at any time.

We appreciate your understanding and participation.

Best Regards,
Cheri Srikanth.

[v-csrikanth]

Hi @RoswithaR 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[RoswithaR]

Hello @v-csrikanth ,

many thanks for your response. Currently I am working on another project. I presume I will return to this project in about 2-3 months and then you can definitely count on my feedback.

Kind regards,

Rosvita

[v-csrikanth]

Hi @RoswithaR 

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @RoswithaR 

I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth · Accepted solution]

Hi @RoswithaR 
Thanks for reaching out to Fabric community.
Sorry for the late response.

You can achieve the sum of new, returning and retentive customers as follows-

1. Keep the order date
2. Add 3 measures for New, returning and retentive customers 
3. Remove Orders 

When you try to group-by Month as (YYY-MMM) the table will group-by Month-year and give the count for its respective month. This will not give the correct count as per Customer segregation.

Therefore, its possible to show the second table with Order date.

Attached Pbix for reference.

If the above information helps you, please give us a Kudos and marked the Accept as a solution.

Best Regards,
Community Support Team _ C Srikanth.

[PijushRoy]

Hi @RoswithaR 

CAn you please share the sample PBIX file Google Drive/One Drive/Dropbox link?

[RoswithaR]

Hi @PijushRoy , thanks for your response. Here is the link: https://www.dropbox.com/scl/fi/3xv3qn2rlbhxhl5t463l5/CustKPIs_Test.pbix?rlkey=2ruhwkz4rb5pp9ju7pmlgr...