Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
banthorpe
Helper I
Helper I

Time difference between adjacent rows

OK - so I'm trying to do something with a table in PowerBI that I hope will be easier than trying in Excel!

 

I have a table which contains meeting start and end datetimes for a number of rooms.

 

What I need to determine is the time difference between the end of one meeting and the start of the next one in the SAME room on the SAME day

 

I have loaded the tables and am using a slicer to filter based on the room ID so I can see all data relating to a specific room but I'm not sure how to write the DAX to calculate the different that I want.

 

Can someone advise please if this is possible and if so how?

 

-Dave

powerbi1.png

1 ACCEPTED SOLUTION

Try with "," per ";"

 

They are calculated columns and try with this code better:

 

Dif = 
var DStart=DIFDates[origStartDate]
var DEnd=DIFDates[origEndDate]
var Room=DIFDates[ROOM]
var DBefore=CALCULATE(MAX(DIFDates[origEndDate]);FILTER(DIFDates;DIFDates[ROOM]=Room && DIFDates[origEndDate]<=DStart && DIFDates[origStartDate].[Day]=DIFDates[origEndDate].[Day] && DIFDates[origStartDate].[Month]=DIFDates[origEndDate].[Month] && DIFDates[origStartDate].[Year]=DIFDates[origEndDate].[Year]))
return
DATEDIFF(DBefore;DIFDates[origStartDate];MINUTE)/60

Best Regards,
Miguel

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

4 REPLIES 4
ZunzunUOC
Resolver III
Resolver III

Hi @banthorpe , maybe it helps you:

 

Dif = 
var DStart=DIFDates[origStartDate]
var DEnd=DIFDates[origEndDate]
var Room=DIFDates[ROOM]
var DBefore=CALCULATE(MAX(DIFDates[origEndDate]);FILTER(DIFDates;DIFDates[ROOM]=Room && DIFDates[origEndDate]<=DStart))
return
IF((DATEDIFF(DBefore;DIFDates[origStartDate];MINUTE)/60)>=24;BLANK();(DATEDIFF(DBefore;DIFDates[origStartDate];MINUTE)/60))

reply008.png

 

Best Regards,
Miguel

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks - would this be a column or a measure?

I get syntax errors: "The syntax for ';' is incorrect..."

Try with "," per ";"

 

They are calculated columns and try with this code better:

 

Dif = 
var DStart=DIFDates[origStartDate]
var DEnd=DIFDates[origEndDate]
var Room=DIFDates[ROOM]
var DBefore=CALCULATE(MAX(DIFDates[origEndDate]);FILTER(DIFDates;DIFDates[ROOM]=Room && DIFDates[origEndDate]<=DStart && DIFDates[origStartDate].[Day]=DIFDates[origEndDate].[Day] && DIFDates[origStartDate].[Month]=DIFDates[origEndDate].[Month] && DIFDates[origStartDate].[Year]=DIFDates[origEndDate].[Year]))
return
DATEDIFF(DBefore;DIFDates[origStartDate];MINUTE)/60

Best Regards,
Miguel

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.