Length Between Purchases

[pbi_baller]

Hi Experts,

I am trying to calculate the length between all customers first and second purchase. Here is a measure I have:

VAR temptable =

ADDCOLUMNS (

VALUES ( 'Table'[date] ),

"RANK", RANKX (

VALUES ( 'Table'[date] ),

'Table'[date],

,

DESC,

DENSE

)

)

RETURN 

DATEDIFF (

MINX ( FILTER ( temptable, [RANK] = 2 ), 'Table'[date] ),

MINX ( FILTER ( temptable, [RANK] = 1 ),'Table'[date]),

DAY

) )

This works when put in a table with all customer id's and evaluates each customer indivdually.

Does anyone know how I can write a measure that will calculate a single figure which sums the length for all customers.

I.e. customer 1 has a length of 14 days and customer 2 has a length of 1 day. The measure will then display a figure of 15. 

Thanks in advance!

[rajulshah]

Hello @pbi_baller ,

Can you please share your sample data structure?

[pbi_baller]

Fact table looks like this:

Date is measure above refers to purchase date.

I hope that helps, cannot share the file for confidential reasons

[v-rzhou-msft · Accepted solution]

Hi @pbi_baller ,

I suggest you to create a new measure based on the original measure you created before.

Diff with correct total = SUMX(VALUES('Table'[Customer ID]),[Basic])

Result is as below.

Best Regards,
Rico Zhou

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