Forum Discussion

redbrumby's avatar
redbrumby
Regular Visitor
8 years ago
Solved

Calculate date difference between first and last date by ID

Hi, 

 

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

 

Event DateID
26/07/20167076341
26/07/20167055292
2/08/20167055292
20/09/20167076341
25/10/20167076341
22/11/20167076341
16/12/20167076341
23/12/20167311257


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,

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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)
    )

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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)
    )

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    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.