topic Split Rows for Entries Greater Than 24 Hours in DAX Commands and Tips

Split Rows for Entries Greater Than 24 Hours

Fabi_Rntmn — Mon, 27 Jun 2022 08:47:23 GMT

Hello Together,

imagine I have the following table consisting of start times and end times where one entry is larger than 24 hours (here 48 hours):

How can I cut this entry in this case into 3 entries for each day such that I have:

1) 11.06.2022 06:00:00 - 11.06.2022 23:59:59

2) 12.06.2022 00:00:00 - 12.06.2022 23:59:59

3) 13.06.2022 00:00:00 - 11.06.2022 06:00:00

Thank you very much for your help, I really appreciate it!

Re: Split Rows for Entries Greater Than 24 Hours

tamerj1 — Mon, 27 Jun 2022 17:46:03 GMT

Hi Fabi_Rntmn
This is a bet complex. Please refer to sample file with the solution https://we.tl/t-rgdc467UOW

New Data = VAR T1 = GENERATE ( Data, VAR StartTime = Data[StartTime] VAR EndTime = Data[EndTime] VAR StartDate = DATE ( YEAR ( StartTime ), MONTH ( StartTime ), DAY ( StartTime ) ) VAR EndDate = DATE ( YEAR ( EndTime ), MONTH ( EndTime ), DAY ( EndTime ) ) VAR HoursToAdd = TIME ( 23, 59, 59 ) RETURN ADDCOLUMNS ( CALENDAR ( StartTime, EndTime ), "@NewStartTime", IF ( [Date] = StartDate, StartTime, [Date] ), "@NewEndTime", IF ( [Date] = EndDate, EndTime, [Date] + HoursToAdd ) ) ) RETURN SELECTCOLUMNS ( T1, "Start Time", [@NewStartTime], "End Time", [@NewEndTime], "Time Difference", DIVIDE ( DATEDIFF ( [@NewStartTime], [@NewEndTime], SECOND ), 3600 ) )

Re: Split Rows for Entries Greater Than 24 Hours

Fabi_Rntmn — Tue, 28 Jun 2022 08:00:00 GMT

Hi tamerj1
Thank you so much for your quick help! This worked quite well 🙂