I have a large amount of data (8900 rows of data) that is categorized at 3 different levels (company, department, team). I have created an index for all items as well as an index for the items that are within the same team. I have a start date and end date for each item in the data. I need to know what the downtime is between each record within each specific team. For example, for Team A their first record ended on 01/01/2019 and their second record started on 02/15/2019, what is the downtime gab between when #1 ended and #2 started, etc.
I have also tried to just simply return the previous record's end date from #1 into a new column of #2 without success. Again the filtering has me baffled.
While both formulas I am using ARE returning data it is not "filtered/grouped" data so it is not accurately reflecting what I am looking for.
I can not for the life of me figure out how to get it to filter the data first then run the datediff... etc.
Here is my datediff formula for the downtime...
Downtime = DATEDIFF(MAXX(FILTER(ALL('Department Change Data'),'Department Change Data'[TeamLevelIndex]=EARLIER('Department Change Data'[TeamLevelIndex])&&[Actual End date]<EARLIER('Department change Data'[Created Date Time])),[Actual End Date]),[Created Date Time],DAY)
Here is my formula trying to return the previous record's end date:
Date_Ended_prev = var currentteamindex = 'Department Change Data'[TeamLevelIndex]
var currentTeam = 'Department Change Data'[Team]
return CALCULATE( MAX('Department Change Data'[Actual End Date])
,FILTER('Department Change Data', 'Department Change Data'[Team]=currentTeam) ,'Department Change Data'[Index] = currentTeamIndex - 1)
My ultimate end goal is to determine what the average number of days are between Changes for each team within a department and company.
If I can get one or the other to work I would be golden! Thanks in advance for any help!
