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

[Anonymous]

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!

[Greg_Deckler]

@Anonymous 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[Anonymous]

@Greg_Deckler Thanks so much I'll give these a shot in the morning!

[Anonymous]

Not certain what I've done wrong, but it just continues past one year. I'm sure it's something I've missed here's the full example.

VAR tmpCalendar = ADDCOLUMNS('DIM Date',"DDate",DATEVALUE('DIM Date'[Date]))
VAR tmpBilling = ADDCOLUMNS('Sales',"StartDate",DATEVALUE(MIN('Sales'[Date])),
                                      "EndDate",DATEVALUE(MIN('Sales'[Date])+365))
VAR tmpTable = 
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpBilling,
            SUMMARIZE(tmpCalendar,[DDate])
        ),
        [DDate] >= [StartDate] &&
        [DDate] <= [EndDate]
    ),
    "Customer",'Sales'[Customer],
    "Date", [Date],
    "Amount", 'Sales'[Revenue]
)
RETURN SUMX(tmpTable,[Amount])