Split rows by days into multiple by dates range

[adicarmeli_]

Hello,
I have data that includes an [Job], [StartDate] and [EndDate].
The time elapsed between the [StartDate] and [EndDate] can span over multiple days.
In such case, I need to split the record into multiple records, so I get a single date in each row.
If the [EndDate] exceeds midnight, than midnight becomes the [EndDate and Time] for that specific row.
For example,
Original row:
[Job]=2338069, [StartDate]=15/11/2022 11:09:00, [EndDate]=17/11/2022 11:01:00
Should become:
[Job]=2338069, [StartDate]=15/11/2022 11:09:00, [EndDate]=15/11/2022 23:59:59
[Job]=2338069, [StartDate]=16/11/2022 00:00:00, [EndDate]=16/11/2022 23:59:59
[Job]=2338069, [StartDate]=17/11/2022 00:00:00, [EndDate]=17/11/2022 11:01:00

The final goal is to calculate the duration of all [Job] per day.
I prefer doing it with DAX.

Any assistance would be greatly appreciated.
Adi

[danextian]

Hi @adicarmeli_ ,

In PQ,

This can be done with two custom column in PQ but more is better so you'll  understand what is happening in  each step. The first column is to count how many days are within the start and end dates. The second column to create a list that can be expanded to into rows. And two more columns to get the new start and end dates.

Paste the following in advanced editor in a blank query. Make sure that everythign else is deleted before pasting.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTVNzTUNzIwMlIwNLQysLQyMFDSUTI0RxE1BInGxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type datetime}, {"End Date", type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Max Number of Rows", each let 
start = Date.From([Start Date]),
end = Date.From([End Date]),
days = Duration.Days(end - start)+1
in days, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Rows", each {1..[Max Number of Rows]}),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Rows"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Rows", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Start Date2", each if [Rows] =1 then [Start Date]  else DateTime.From(Date.AddDays( Date.From([Start Date]), [Rows]-1 )), type datetime),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "End Date 2", each if [Rows] = [Max Number of Rows] then [End Date]  else DateTime.From(Number.From(Date.From([Start Date2]))+1-(1/86400)), type date)
in
    #"Added Custom3"

[klmm00151151]

cheers, mate!

you rock!

[myon]

Sorry for a non-answer....but I strongly suggest you solve this as close to source as possible, talking from experience with the same problems to solve. SQL with a recursive common table expression is prefferred. Power Query is possible also.

I do not remember exactly how I solved it with DAX. It involved keeping the structure you had in your original table, De Morgan's Law for the problem of overlapping time periods comparing a non-relational calendar with start and stop times for each day and doing subtraction against the end of the day if the periods overlapped. Was super slow and made me realize that this was not the strength of DAX.