How to make a calculated column of time between purchases of the same product, by the same costumer?

[Awikant]

I am trying to make Power BI search for any identical rows, except for the "date/time" values, and report the time between them in a new column. So i want new column that calculate the time between two rows (by the difference in a "Time/Date" column), provided that several other values are identical (e.g. same costumer-ID, same product, same store) IF such rows exists.

In other words, if the costumer-ID, product-ID and Store-ID are the same as in an earlier row, calculate the time (e.g. days, months or years) that has past since the earlier row. In the example table below, i would like to have the calculated column return a value only in row 4 (the time difference between row 1 and row 4). 

Index	Time/date	Costumer_ID	product_ID
1	25.03.2005	1	D
2	26.03.2005	2	B
3	29.03.2005	1	A
4	04.04.2005	1	D

Any ideas?

[Mariusz]

Hi @Awikant 

You can add a column like below.

column = 
VAR __thisRowDate = 'Table'[Time/date]
VAR __previousDate =
    CALCULATE(
        MAX( 'Table'[Time/date] ),
        ALLEXCEPT( 'Table', 'Table'[Costumer_ID], 'Table'[product_ID] ),
        'Table'[Time/date] < __thisRowDate   
    )
RETURN IF( __previousDate > 0, __thisRowDate - __previousDate )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Greg_Deckler · Accepted solution]

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395.... You have essentially the same scenario.