Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

New Customer Pattern Question - Modify to Consider Start Date Range Instead of Single Date

Hello everyone,

I've beeing using an example from dax pattern to attempt to display a matrix where new customer revenue begins on the start date for each customer and ends after one year. The examples below work perfectly, what I would need them to do is instead of considering only the start date one time, to continue to display for one year and then stop.

So to sum up the question, in the below example, is there a way to have it consider a range instead of using MIN( date )? I've I use something like datesbetween or datesinperiod it throws an error saying one value was expected and there were many or there is a data mashup error.

Thanks so much everyone,
datafuntime

 

 

Date New Customer :=
CALCULATE (
    MIN ( Sales[Order Date] ),
    ALLEXCEPT (
        Sales,
        Sales[CustomerKey],
        Customer
    )
)

 

 

 

 

 

# New Customers :=
VAR CustomersWithNewDate =
    CALCULATETABLE (                        -- Prepares a table that 
        ADDCOLUMNS (                        -- for each customer contains
            VALUES ( Sales[CustomerKey] ),  -- the date of their first purchase ever
            "@NewCustomerDate", [Date New Customer]
        ),
        ALLSELECTED ( Customer ),           -- Regardless of local filters on customer
        ALLSELECTED ( 'Date' )              -- and on date
    )
VAR CustomersWithLineage =                  -- Here we change the data lineage
    TREATAS (                               -- of the CustomersWithNewDate variable
        CustomersWithNewDate,               -- so that it will filter the 
        Customer[CustomerKey],              -- Customer table and the
        'Date'[Date]                        -- Date table
    )
VAR Result =                                
    CALCULATE (                            
        DISTINCTCOUNT ( Sales[CustomerKey] ), -- Counts the number of customers only
        KEEPFILTERS ( CustomersWithLineage )  -- if included in @NewCustomerDate variable
    )
RETURN
    Result

 

 



The output is considering each one only the first time as you can see, I hope to be able to have it considered upon first appearance continuing out one year then stopping.


Thanks so much!

3 Replies