AVERAGE DATEDIFF between two dates (two events and one column)

[NumeroENAP]

Hi, 

I want a dynamic measure to be able to calculate the AVERAGE difference between two dates of a same column. The trick is that the calculation is between two status, which are "First contact" and "Contacted". The second trick is that there could be more that one subscribtion for the same ID (so you need to pick the last date per status).

Here's my draft :
 

CALCULATE(

AVERAGEX(
    TABLE, 
          DATEDIFF(
               CALCULATE(LASTDATE(TABLE[Date]) , TABLE[Status]="First contact") , 
               CALCULATE(LASTDATE(TABLE[Date]) , TABLE[Status]="Contacted") , 
               DAY))
FILTER(
   CALCULATE(LASTDATE(TABLE[Date]) , TABLE[Status]="First contact") < CALCULATE(LASTDATE(TABLE[Date]) ,
   TABLE[Status]="Contacted")
)

But, of course, the result is blank...

Here's my data sample.

Index	ID	Name	Status	Date
1	2222200	Name1	First contact	2020-10-01
2	2222201	Name2	First contact	2020-10-02
3	2222202	Name3	First contact	2020-10-03
4	2222200	Name1	Contacted	2020-10-12
5	2222201	Name2	Contacted	2020-10-12
6	2222202	Name3	Contacted	2020-10-12
7	2222203	Name4	Contacted	2020-10-13
8	2222200	Name1	Subscribed	2020-11-24
9	2222200	Name2	Subscribed	2020-11-25
10	2222200	Name3	Subscribed	2020-11-26

Can you help me?

Thank you

[lbendlin]

Use variables to construct your logic step by step

- create a table variable that holds all unique IDs

- add columns that hold the latest "First Contact" date for each ID and the latest "Contacted" date for the same ID

- eliminate rows where either of these fields are missing

- calculate the averagex of the date diff for the remaining rows.

Let me know if you would like help with the DAX code.