Forum Discussion
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
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.
4 Replies
Hi Anonymous , 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))
Best Regards,
Miguel
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.