Determine how many customers purchased different items, and the period of time between doing so?

[D_PBI]

Hi,
I have the below dataset. I would like to understand, via DAX, how to ascertain the following answers. Before I list the questions, I should say the report will contain a filter on 'Item'. Choosing the Item of, say, 'Bike' should return the customers whose first purchased item was a 'Bike'.

Questions are:

1) Of the customers who purchased a Bike as their first item, what percentage went of to purchase a Bench, Book, Car, Desk, Rod, and Watch.
2) What was the time period (in days) for those who purchased a Bike as their first item to purchase their second item, and the next different item and so on?

Answers are:
Based on the dataset shown above, the answers to my questions would be:
1)
a) Total customers who purchased a Bike as their first item: 2 (that being Dave and Paul).
b) Total customers who purchased a Bike as their first item but also purchased a Bench: 1 (just Paul).
c) Total customers who purchased a Bike as their first item but also purchased a Book:   0
d) Total customers who purchased a Bike as their first item but also purchased a Car:     1 (just Paul).
e) Total customers who purchased a Bike as their first item but also purchased a Desk:   0
f) Total customers who purchased a Bike as their first item but also purchased a Rod:     1 (just Dave).
g) Total customers who purchased a Bike as their first item but also purchased a Watch: 1 (just Paul).

2) 
a) Days between purchasing the first Bike and the first Bench:     Paul 731 days.  Dave n/a.
b) Days between purchasing the first Bike and the first Book:      Paul n/a.  Dave n/a.
c) Days between purchasing the first Bike and the first Car:         Paul 1157 days.  Dave n/a.
d) Days between purchasing the first Bike and the first Desk:      Paul n/a.  Dave n/a.
e) Days between purchasing the first Bike and the first Rod:        Paul n/a.  Dave 336 days.
f) Days between purchasing the first Bike and the first Watch:     Paul 734 days.  Dave n/a.

I appreciate this will need a number of DAX measures. The aim is to use Context Evaluation so when I bring the measure to work out, say, the days between the first item purchase and the different item purchased, then I would drop that measure on a row for Dave and the calculation is worked out.
Obviously, nothing is to be hard-code as the CustomerID, PurchaseDate, and Item will change overtime.

I hope I've explained all well. Please help as I'm stuck on this.
Thanks in advance.

[Greg_Deckler · Accepted solution]

@D_PBI Try something along the lines of:

Bikes First Measure =
  VAR __Customers = DISTINCT('Table',[CustomerID])
  VAR __Table = 
    GENERATE(
      __Customers,
      VAR __CustomerID = [CustomerID]
      VAR __FirstPurchaseDate = MINX(FILTER('Table',[CustomerID] = __CustomerID),[PurchaseDate])
      VAR __FirstPurchase = MINX(FILTER('Table',[CustomerID] = __CustomerID && [PurchaseDate] = __FirstPurchaseDate),[Item])
      RETURN ROW(
        "FirstPurchaseDate", __FirstPurchaseDate,
        "FirstPurchase", __FirstPurchase
      )
    )
  VAR __Result = COUNTROWS(FILTER(__Table,[FirstPurchase] = "Bike")) // or use CONCATENATEX to return customer id's
RETURN
  __Result
        

You can use similar measures for the rest, here is an example of finding customers who also bough Bench:

Bikes First Measure =
  VAR __Customers = DISTINCT('Table',[CustomerID])
  VAR __Table = 
    GENERATE(
      __Customers,
      VAR __CustomerID = [CustomerID]
      VAR __FirstPurchaseDate = MINX(FILTER('Table',[CustomerID] = __CustomerID),[PurchaseDate])
      VAR __FirstPurchase = MINX(FILTER('Table',[CustomerID] = __CustomerID && [PurchaseDate] = __FirstPurchaseDate),[Item])
      VAR __PurchasedBench = COUNTROWS(FILTER('Table',[CustomerID] = __CustomerID && [Item] = "Bench")) + 0
      RETURN ROW(
        "FirstPurchaseDate", __FirstPurchaseDate,
        "FirstPurchase", __FirstPurchase,
        "PurchasedBench", __PurchasedBench
      )
    )
  VAR __Result = COUNTROWS(FILTER(__Table,[FirstPurchase] = "Bike" && [PurchasedBench] > 0)) // or use CONCATENATEX to return customer id's
RETURN
  __Result

And for finding days between:

Bikes First Measure =
  VAR __Customers = DISTINCT('Table',[CustomerID])
  VAR __Table = 
    GENERATE(
      __Customers,
      VAR __CustomerID = [CustomerID]
      VAR __FirstPurchaseDate = MINX(FILTER('Table',[CustomerID] = __CustomerID),[PurchaseDate])
      VAR __FirstPurchase = MINX(FILTER('Table',[CustomerID] = __CustomerID && [PurchaseDate] = __FirstPurchaseDate),[Item])
      VAR __PurchasedBench = COUNTROWS(FILTER('Table',[CustomerID] = __CustomerID && [Item] = "Bench")) + 0
      VAR __PurchasedBenchDate = MINX(FILTER('Table',[CustomerID] = __CustomerID && [Item] = "Bench"),[PurchaseDate])
      VAR __DaysBetween = (__PurchaseBenchDate - __FirstPurchaseDate) * 1.
      RETURN ROW(
        "FirstPurchaseDate", __FirstPurchaseDate,
        "FirstPurchase", __FirstPurchase,
        "PurchasedBench", __PurchasedBench,
        "PurchasedBenchDate", __PurchasedBenchDate,
        "DaysBetween", __DaysBetween
      )
    )
  VAR __Result = AVERAGEX(FILTER(__Table,[FirstPurchase] = "Bike" && [PurchasedBench] > 0), [DaysBetween])
RETURN
  __Result

[v-zhangti · Accepted solution]

Hi, @D_PBI 

You can try the following methods.
Measure:

First Item = 
Var _mindate=CALCULATE(MIN('Table'[PurchaseDate]),ALLEXCEPT('Table','Table'[CustomerName]))
Return
CALCULATE(MAX('Table'[Item]),FILTER(ALL('Table'),[PurchaseDate]=_mindate))

First Bike and Item % = 
VAR _N1 = CALCULATE ( DISTINCTCOUNT ( 'Table'[CustomerName] ),
        FILTER ( ALL ( 'Table' ), [First Item] = "Bike" ) )
VAR _N2 = CALCULATE ( DISTINCTCOUNT ( 'Table'[CustomerName] ),
        FILTER ( ALL ( 'Table' ),
            [First Item] = "Bike"
                && [Item] = SELECTEDVALUE ( 'Table'[Item] ) ) )
RETURN
    DIVIDE ( _N2, _N1 ) + 0

Days first Bike and the other = 
VAR _firstbikedate = CALCULATE ( MIN ( 'Table'[PurchaseDate] ),
        FILTER ( ALL ( 'Table' ),
            [First Item] = "Bike"
                && [CustomerName] = SELECTEDVALUE ( 'Table'[CustomerName] ) ) )
VAR _firstotherdate = CALCULATE ( MIN ( 'Table'[PurchaseDate] ),
        FILTER ( ALL ( 'Table' ),
            [First Item] = "Bike"
                && [Item] = SELECTEDVALUE ( 'Table'[Item] )
                && [CustomerName] = SELECTEDVALUE ( 'Table'[CustomerName] ) ) )
VAR _days = DATEDIFF ( _firstbikedate, _firstotherdate, DAY )
RETURN
    IF ( _days <> BLANK (), _days + 1, BLANK () )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[D_PBI]

@v-zhangti - thanks for your response. I haven't attempted the suggestion as Greg_Deckler responded first and I was in the process of following his guidance, which has worked, when you added yours.

[Greg_Deckler · Accepted solution]

@D_PBI Try something along the lines of:

Bikes First Measure =
  VAR __Customers = DISTINCT('Table',[CustomerID])
  VAR __Table = 
    GENERATE(
      __Customers,
      VAR __CustomerID = [CustomerID]
      VAR __FirstPurchaseDate = MINX(FILTER('Table',[CustomerID] = __CustomerID),[PurchaseDate])
      VAR __FirstPurchase = MINX(FILTER('Table',[CustomerID] = __CustomerID && [PurchaseDate] = __FirstPurchaseDate),[Item])
      RETURN ROW(
        "FirstPurchaseDate", __FirstPurchaseDate,
        "FirstPurchase", __FirstPurchase
      )
    )
  VAR __Result = COUNTROWS(FILTER(__Table,[FirstPurchase] = "Bike")) // or use CONCATENATEX to return customer id's
RETURN
  __Result
        

You can use similar measures for the rest, here is an example of finding customers who also bough Bench:

Bikes First Measure =
  VAR __Customers = DISTINCT('Table',[CustomerID])
  VAR __Table = 
    GENERATE(
      __Customers,
      VAR __CustomerID = [CustomerID]
      VAR __FirstPurchaseDate = MINX(FILTER('Table',[CustomerID] = __CustomerID),[PurchaseDate])
      VAR __FirstPurchase = MINX(FILTER('Table',[CustomerID] = __CustomerID && [PurchaseDate] = __FirstPurchaseDate),[Item])
      VAR __PurchasedBench = COUNTROWS(FILTER('Table',[CustomerID] = __CustomerID && [Item] = "Bench")) + 0
      RETURN ROW(
        "FirstPurchaseDate", __FirstPurchaseDate,
        "FirstPurchase", __FirstPurchase,
        "PurchasedBench", __PurchasedBench
      )
    )
  VAR __Result = COUNTROWS(FILTER(__Table,[FirstPurchase] = "Bike" && [PurchasedBench] > 0)) // or use CONCATENATEX to return customer id's
RETURN
  __Result

And for finding days between:

Bikes First Measure =
  VAR __Customers = DISTINCT('Table',[CustomerID])
  VAR __Table = 
    GENERATE(
      __Customers,
      VAR __CustomerID = [CustomerID]
      VAR __FirstPurchaseDate = MINX(FILTER('Table',[CustomerID] = __CustomerID),[PurchaseDate])
      VAR __FirstPurchase = MINX(FILTER('Table',[CustomerID] = __CustomerID && [PurchaseDate] = __FirstPurchaseDate),[Item])
      VAR __PurchasedBench = COUNTROWS(FILTER('Table',[CustomerID] = __CustomerID && [Item] = "Bench")) + 0
      VAR __PurchasedBenchDate = MINX(FILTER('Table',[CustomerID] = __CustomerID && [Item] = "Bench"),[PurchaseDate])
      VAR __DaysBetween = (__PurchaseBenchDate - __FirstPurchaseDate) * 1.
      RETURN ROW(
        "FirstPurchaseDate", __FirstPurchaseDate,
        "FirstPurchase", __FirstPurchase,
        "PurchasedBench", __PurchasedBench,
        "PurchasedBenchDate", __PurchasedBenchDate,
        "DaysBetween", __DaysBetween
      )
    )
  VAR __Result = AVERAGEX(FILTER(__Table,[FirstPurchase] = "Bike" && [PurchasedBench] > 0), [DaysBetween])
RETURN
  __Result

[D_PBI]

@Greg_Deckler - thanks for your prompt response. Very much appreaciated. I've got what I needed working based on your guidance. Kudos and suggestion vote given.