cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
banthorpe Regular Visitor
Regular Visitor

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

Accepted Solutions
ZunzunUOC Member
Member

Re: Time difference between adjacent rows

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

Re: Time difference between adjacent rows

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.

banthorpe Regular Visitor
Regular Visitor

Re: Time difference between adjacent rows

Thanks - would this be a column or a measure?

banthorpe Regular Visitor
Regular Visitor

Re: Time difference between adjacent rows

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

ZunzunUOC Member
Member

Re: Time difference between adjacent rows

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 255 members 2,373 guests
Please welcome our newest community members: