Measure Number of Days Between Customer Purchases Using Item Filter

[chuckle]

Hi all,

I am trying to create a measure that shows the number of days between:

A) The MAX transaction date that each customer buys a specific product.  This product is selected by a filter.  And

B) Each subsequent transaction made by that customer, for every single product (not just the filtered item).

In the below example, Product A has been selected using the filter.

Is this possible using DAX?

[Anonymous · Accepted solution]

Hi @chuckle,

Please check following steps and see if the result achieve your expectation:

1. Create calculated table as slicer:

Table 2 = DISTINCT('Table'[product])

2. Create measure:

Measure =

var sv = SELECTEDVALUE('Table 2'[product])

var a = CALCULATE(MAX('Table'[date]),FILTER(ALL('Table'),'Table'[customer] = MAX('Table'[customer])&&'Table'[product] = sv))

var b = CALCULATE(MAX('Table'[date]),FILTER(ALL('Table'),'Table'[customer] = MAX('Table'[customer])&&'Table'[product] = MAX('Table'[product])))

return

IF(NOT(ISFILTERED('Table 2'[product])),"",IF(MAX('Table'[product]) = sv,"",IF(MAX('Table'[date])=b,DATEDIFF(a,MAX('Table'[date]),DAY),"")))

3. Result would be shown as below:

BTW, Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[chuckle]

This is a brilliant starting point @Anonymous , thank you for your help!