Problem: DAX Patterns New Customers -- customer orders seem to disappear in detail

[powerquest1234]

Problem: DAX Patterns New Customers -- customer orders seem to disappear in detail

Business case: My user wants to see a matrix visual that shows customers who made their first ever order to our company this year. It might look something like below:

This slicer has the field 'Sheet1'[Order Date] in it.

The summary matrix has the 'Sheet1'[Customer Key] for rows and [New Customer Orders] for Values.

The detail table has the following columns:  'Sheet1'[Customer Key], 'Sheet1'[Order Date], 'Sheet1'[Order Number], [New Customer Orders]. I intended for New Customer Orders to filter the table to new customers for 2023, but it is filtering to those new customers' orders that are associated with their first order date in 2023.

The summary table is correct, but the detail table seems to be omitting some of the new customers' orders that were submitted this year. The user would like to see every order submitted by the new customer this year, just not the orders associated with that new customers' first order date.

I used SQL BI's New and Returning Customers Pattern, but it doesn't quite seem to fit what I am doing: https://www.daxpatterns.com/new-and-returning-customers/

My user, for example, doesn't want break-outs of new customers by month. She just wants a count of the orders submitted so far this year by customers who made their first order in 2023.

This is the DAX I wrote based off of the Dynamic Absolute pattern:

Date New Customer =

CALCULATE(

  MIN('Sheet1'[Order Date])

  , USERELATIONSHIP('Date'[Date], 'Sheet1'[Order Date])

  , ALLEXCEPT(

      'Sheet1'

        , 'Sheet1'[Customer Key]

    )

 )

New Customer Orders  =

VAR CustomersWithNewDate =

    CALCULATETABLE (                       -- Prepares a table that

        ADDCOLUMNS (                      

            VALUES ( 'Sheet1'[Customer Key] ),  -- for each customer contains

            "@NewCustomerDate"

                , [Date New Customer] -- the date of their earliest order submission

        )

    )

VAR NewCustomers =

       ADDCOLUMNS(

        FILTER (                              

            CustomersWithNewDate,              -- Filters the customers

            [@NewCustomerDate]                 -- whose new customer date

                IN VALUES ( 'Sheet1'[Order Date] )     -- falls within the current period

        )

        , "Order Total"

            , [Total Orders]

    )

 VAR Result =                               -- The count of the new customers

    SUMX(

        NewCustomers

         , [Order Total]

    )

RETURN

    Result

Total Orders = COUNT('Sheet1'[Order Number])

I think my problem has sometihng to do with the  [Date New Customer] variable filtering to the customers' earliest dates, but I'm not sure how to tell DAX to make sure to include the new customers' orders for all of 2023 in the Detail table.

Dummy model screenshot:

Note that Sheet1 has three columns, Customer Key, Order Date, and Order Number.

There is an inactive relationship between Sheet1's Order Date and Date's Date field.

Dummy data:

Order Date	Customer Key	Order Number
5/5/2023	1	A
5/5/2023	1	B
5/7/2023	1	C
3/1/2022	2	D
4/1/2023	2	E
4/2/2023	3	F
4/2/2023	3	G
4/3/2023	4	H
4/5/2023	4	I
4/7/2023	4	J
4/8/2023	4	K

[amitchandak]

@powerquest1234 , Please refer to approaches I have use, change periods as per need

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retentio...

Learn Power BI Advance - Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q

[powerquest1234]

@amitchandak I'm sorry, but I'm not seeing how this helps with my problem.

For my dummy data, I adapted one of your formulas like so:

(Assume there is a slicer on screen where the user can select single 'Date'[Year] values)

Year To Date =
CALCULATE(
        [Total Orders]
       ,DATESINPERIOD('Date'[Date],max('Date'[Date]),-1,YEAR)
       , USERELATIONSHIP('Sheet1'[Order Date], 'Date'[Date])
)  

Prior to Year To Date =
CALCULATE(
     [Total Orders]
     ,DATESINPERIOD('Date'[Date],MIN('Date'[Date]),-30,YEAR)
    , USERELATIONSHIP('Sheet1'[Order Date], 'Date'[Date])
)  

I am able to successfully get to a count of customers by adapting the SUMX:

New Customer This Period =

SUMX(

    VALUES('Sheet1'[Customer Key])

    , IF(ISBLANK([Prior to Year To Date]) && NOT(ISBLANK([NEW Year To Date]))

        , 1

        , BLANK()

    )

)

However, this also seems to return a different detail table than I want. The total of new customers (3) is correct, but the table detail is not correct.

- As you can see below, Customer 2's order that started in 2023 is pulled in, but I don't want Customer 2 included at all because Customer 2 had an order in 2022 and is therefore not a new customer.

This is the result table I am looking for:

Dummy data:

Order Date	Customer Key	Order Number
5/5/2023	1	A
5/5/2023	1	B
5/7/2023	1	C
4/2/2023	3	F
4/2/2023	3	G
4/3/2023	4	H
4/5/2023	4	I
4/7/2023	4	J
4/8/2023	4	K