Calculating Non First Order Of the customer

DAX DAX Calculations
3

Hi, @MAAbdullah47 !

Here a short example of the way, how you could calculate sales after first order date.
I’ve also attached PBIX for you, to be more clear. In sample model i also have product category, subcategory and customers (it is AdwentureWorks sample).

What i’ve done:

  1. Calculate dynamic FirstSalesDate (which is changed in every Month / Day of Calendar) using MINX and Summarize (measure [01 First purchase date (dynamic)])
    01 First purchase date (dynamic) =
    MINX(
    – gives us opportunity to build virtual table we needed
    SUMMARIZE(
    – creates virtual table of required detalisation
    ‘Sales’,
    ‘Customer’[CustomerKey],
    – we’d like to drillthrough Customers and Product
    ‘Product’[ProductKey],
    – and we add date to be calculated
    ‘Date’[Date]
    ),
    – as min from virtual table
    ‘Date’[Date]
    )
  2. Next i use Calculate and ALL to change from “dynamic” to “absolute” - so i can see i every selected period First Sales Date (measure called [02 02 First purchase date (absolute)])
    02 First purchase date (absolute) =

– use Calculate to add filter to measure

– ALL clears all date filters

CALCULATE([01 First purchase date (dynamic)],ALL(‘Date’))

  1. We need to have Start date for calculations after first sales date
    So here simply i use DATE function and add +1 day to date
    CalcSalesStartDate =
    IF ([03 Revenue]<=0,BLANK(),
    DATE(YEAR([02 First purchase date (absolute)]),MONTH([02 First purchase date (absolute)]),DAY([02 First purchase date (absolute)])+1)
    )

  2. And we need to calculate end date - here i use TODAY () or you could use any other date variance you’d like to have

  3. And finally we go to calculate measure called [04 Revenue after first order]
    where i used CALCULATE and DATESBETWEEN to setup revenue calculations from StartDate till EndDate
    04 Revenue after first order =
    CALCULATE(
    [03 Revenue],
    DATESBETWEEN(‘Date’[Date],[CalcSalesStartDate],[CalcSalesEndDate])
    )

Also, you could see some other scenarios regarding your questions: First Purchase Date - DAX

Hope, it helps you!

BR,
Oleg
SalesAfterFirstOrderDate.pbix (7.8 MB)