Forum Discussion

SamBrown17's avatar
SamBrown17
Icon for Helper I rankHelper I
4 years ago
Solved

How to calculate Date diff with data setup

Hi Team,   I have the example data below,   I have a database full of record types which are jobs in my system, I want to calculate the average time or date difference between a set sequence of t...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    SamBrown17 Right, it's the same principle but yours will look something like this:

    Column = 
  VAR __record = [record_id]
  VAR __type = [event_type]
  VAR __current = [created_at]
  VAR __currentStatus = [To (Status)]
  VAR __previous = 
    IF(__type = "Job status updated",
      SWITCH(__currentStatus,
        "Scheduled",MAXX(FILTER('Table',[record_id] = __record && [From (Status) = "Book" && [To (Status)] = BLANK(),[created_at]),
        "Site Attended",MAXX(FILTER('Table',[record_id] = __record && [From (Status) = "Book" && [To (Status)] = "Scheduled",[created_at])
      )
    )
RETURN
  __current - __previous
mh2587's avatar
mh2587
Icon for Super User rankSuper User
4 years ago

VAR CurrentDate = CALCULATE ( MAX(Table[Created_at] ),
ALL( 'Table'[Record_ID] )

)

var LastDate = CALCULATE(
MAX(Table[Created_at]),
ALL( 'Table'[Record_ID] ),

Table[Created_at] < CurrentDate )

VAR DD = DATEDIFF ( LastDate,CurrentDate, DAY )
RETURN DD