Showing results for 
Search instead for 
Did you mean: 
Regular Visitor

Split rows by days into multiple by dates range

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.



Super User
Super User

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.

    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)
    #"Added Custom3"




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Connect to me on LinkedIn || Need consulting? Hire me for a Power BI gig on UpWork.

cheers, mate!


you rock!

Frequent Visitor

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.

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors