Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

New Member

Finding the DateDiff between to different columns in two different rows AFTER filtering data.

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!


Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors