Calculate date difference between first and last date by ID

[redbrumby]

Hi, 

I have a dataset (much larger and not filtered however) which looks like the following: 

Event Date	ID
26/07/2016	7076341
26/07/2016	7055292
2/08/2016	7055292
20/09/2016	7076341
25/10/2016	7076341
22/11/2016	7076341
16/12/2016	7076341
23/12/2016	7311257

I need to calculate the days between the first and last date for each unique ID. The formula might also need to check if an ID has two dates or not. 

I've explored a few ideas but am not that confident in DAX.

Thanks in advance for your help,

[Ashish_Mathur]

Hi,

i dragged ID to the rows labels of the Table visual and wrote the following measure

Diff = 1*(MAX(Data[Event Date])-MIN(Data[Event Date]))

Hope this helps.

[Phil_Seamark · Accepted solution]

HI @redbrumby

This calculated table may give you want you want.  I have attached a simple PBIX file

Table = 
    SUMMARIZECOLUMNS(
        'Table1'[ID] ,
        "Min Date" , MIN('Table1'[Event Date]) ,
        "Max Date" , MAX('Table1'[Event Date]) ,
        "Date Diff" , DATEDIFF( MIN('Table1'[Event Date]), MAX('Table1'[Event Date]),DAY)
    )