Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
adicarmeli_
Regular Visitor

Split rows by days into multiple by dates range

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

 

screenshot.png

3 REPLIES 3
danextian
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.

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"

 

danextian_0-1683116102113.png

 






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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

cheers, mate!

 

you rock!

myon
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

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.