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
ShaneL79
Helper I
Helper I

Distributing Hours over Months using START/END dates/time.

I just want to point out that I am not comfortable with Power Query, so if at all possible I would prefer answers that involve measures only. If that isn't possible, please let me know.

 

My issue, and one a lot of people seem to be having, is to distribute hours between months when only given a start date and end date.

 

My company tracks hours [WORK HOURS] from when a service call was made [START DATE/TIME] until it was closed [END DATE/TIME]. We report monthly on the service hours. In our November report, the hours showed ~280 hours. 260 of those hours was from a single service call that started on 11/28/2022 and ended on 12/8/2022.

 

All of the hours were allocated to November when realistically the majority were performed in December. (see screenshot below)


I have read/watched all kinds of advice given on this topic, but cannot seem to get it. I am new to PowerBI, so bare with me. 

 

I have built the necessary measures [START DATE/TIME], [END DATE/TIME], [YEAR], [MONTH], [MONTH ORDER], [WORK HOURS]

 

Here is a sample data from my tables:

 

Table 1: Calendar

DATEYEARMONTHNUMBER
11/28/20222022November11
1/1/20212021January1
6/25/20222022June6

 

Table 2: Data

EVENT IDSTART TIMEEND TIMEHOURS
111/28/2022 16:0012/8/2022 12:00236
21/1/2021 07:001/3/2021 14:0055
36/25/2022 22:007/5/2022 08:00208
411/30/2022 12:00  

 

What I would like to see is the following:

 

Event IDStart TimeEnd TimeYearMonthHours
111/28/2022 16:0011/30/2022 23:592022November56
112/1/2022 00:0012/8/2022 12:002022December180
21/1/2021 07:001/3/2021 14:002021January55
36/25/2022 22:006/30/2022 23:592022June100
37/1/2022 00:007/5/2022 08:002022July104
411/30/2022 12:0011/30/2022 23:592022November12
412/1/2022 00:00 2022December 

 

2022-12-21_15-30-57.jpg

 

Thank you in advance. 

1 ACCEPTED SOLUTION

I think it's because you are adding this as a custom column. Not as a function like I told you.

 

From New Source select Blank Query. Name this query on the left box (like changing the name of the source table). Then open Advanced Editor (it's a button next to the Refresh Preview)and paste this:

let getParameters = (StartDate, EndDate) =>
    
    let

        NullStart = StartDate = "" or StartDate = null,
        Start = if NullStart then null else Date.From(StartDate),
        NullEnd = EndDate = "" or EndDate = null,
        End = if NullEnd then null else Date.From(EndDate),

        CountDays = if NullEnd or NullStart then 1 else Duration.Days(End-Start) + 1,

        DateList = if NullStart then {null} else List.Dates(Start, CountDays, #duration(1, 0, 0, 0)),

        #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
        #"Add Custom Start DateTime" = Table.AddColumn(#"Converted to Table", "Start datetime", each if NullStart then null else if [Date] = Start then StartDate else DateTime.From([Date])),
        #"Add Custom End DateTime" = Table.AddColumn(#"Add Custom Start DateTime", "End datetime", each if NullEnd then null else if [Date] = End then EndDate else [Date] & #time(23,59,59)),
        #"Change Types" = Table.TransformColumnTypes(#"Add Custom End DateTime",{{"End datetime", type datetime}, {"Start datetime", type datetime}}),
        #"Duration" = Table.AddColumn(#"Change Types", "Duration", each Duration.TotalMinutes([End datetime]-[Start datetime])/60, type number),
        #"Remove Date" = Table.RemoveColumns(#"Duration",{"Date"})
        
    in
        #"Remove Date"
        
in
    getParameters

After doing that you should see something like this:

bolfri_0-1671843791189.png

 

Then go to your table and from ribbon select new column > invoke custom function:

bolfri_1-1671843849260.png

Select column name, query name that you have provided in previous steps and the start and end columns (example on screenshot).

bolfri_2-1671843911449.png

And you should know the rest. 🙂





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

Proud to be a Super User!




View solution in original post

10 REPLIES 10
bolfri
Super User
Super User

Hi & Hello @ShaneL79,

 

I can't help you with spliting it with eom, but I can help with spliting into days with counting durations.

Step 1. New Source > Blank Query

Step 2. Create a funtion splitting_datetime_to_datetime_with_nullend

 

= (StartDate as datetime, EndDate) =>
    
    let
        Start = Date.From(StartDate),
        NullEnd = EndDate = "" or EndDate = null,
        End = if NullEnd then null else Date.From(EndDate),
        CountDays = if NullEnd then 1 else Duration.Days(End-Start) + 1,
        DateList = List.Dates(Start, CountDays, #duration(1, 0, 0, 0)),

        #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
        #"Add Custom Start DateTime" = Table.AddColumn(#"Converted to Table", "Start datetime", each if [Date] = Start then StartDate else DateTime.From([Date])),
        #"Add Custom End DateTime" = Table.AddColumn(#"Add Custom Start DateTime", "End datetime", each if NullEnd then null else if [Date] = End then EndDate else [Date] & #time(23,59,59)),
        #"Change Types" = Table.TransformColumnTypes(#"Add Custom End DateTime",{{"End datetime", type datetime}, {"Start datetime", type datetime}}),
        #"Duration" = Table.AddColumn(#"Change Types", "Duration", each Number.RoundUp(Duration.TotalMinutes([End datetime]-[Start datetime])/60), type number),
        #"Remove Date" = Table.RemoveColumns(#"Duration",{"Date"})
        
    in
        #"Remove Date"

 

 

HERE COME BACK TO YOUR TABLE THAT YOU WANT TO SPLIT DATES.

 

Step 3. In your data make sure that START TIME and END TIME is datetime type.

bolfri_0-1671760425223.png

Step 4. From a Ribbon Add Column select Invoke Custom Function

Name of the new column is optional. Select created function and set up parameters with your START TIME and END TIME columns.

bolfri_1-1671760479590.png

Step 5. From a SplittingDates column expand all columns with disabled option "Use original column name as prefix"

bolfri_2-1671760597644.png

Step 6. Remove old columns. And this is what you get:

bolfri_3-1671760649276.png

Step 7. Create year and month column from Start datetime. 🙂 

 





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

Proud to be a Super User!




I made it most of the way so far. I have a couple of questions though.

 

I did not modify the query at all. So the start/end dates are not linked to my data. Is this correct?

 

When creating the query it asks me to define start time and an optional end time. I created a start time of 1/1/2017 but left the end time blank. In the table that was created only a single row was created. I then tried entering an end date into the future 12/31/2030 and the table populated by day. Do I need to specify an end date?

 

Once the steps you provided are complete, how do I link in the actual data/measures so those start/end dates are used for durations?

Hi @ShaneL79,

 

I think you've missed the Step 3 or I didn't write it correctly. You have a table (let's call it fact_data) so in that fact_data with fields that you already have prom a Ribbon > Add Column select Invoke Custom Function and provide a columns that represents your startdate and enddate (enddate can be nullable).

 

Try with that and let me know if you will find more issues with description that I've provided.





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

Proud to be a Super User!




You were right. I missed the "YOUR TABLE" part of step 3. 

 

Now doing it that way I get an error when trying to expand the table (re: Step #5). The error is:

 

Expression.Error: We cannot convert the value null to type DateTime.
Details:
Value=
Type=[Type]

 

Any ideas? I did confirm that my START TIME and END TIME were date/time types. 

 

All events where there is no [START TIME] values show as "Error". The others show "Table" prior to performing that step. However, when I do that step the above error pops up. 

 

Lastly, do I need to remove the old columns? They are still connected to several dozen other measures and reports, so I would ideally like to keep them and just have the new ones added to that is possible.

 

 

Getting closer though.. I appreciate your help walking me through, and I apologize for my beginner level understanding of PowerBI. 

No problem. Everyone starts from somewhere. I can understand that.

You said the problem is with records that has null in the Start Date, right? Your sample data didn't provide such scenario. What do you expect to be in the new columns and the durations if the StartDate is null?





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

Proud to be a Super User!




You're right, I didn't say that start time can be null and I should have. It is true though. Some of the events do not require work, so therefore there is no start/end time for those events (the majority of events are like this in fact). 

 

If the start date is null, then duration is 0. The start/end times can be null as well, or anything that may fit better. I am using the dates from other columns for my reports, I just needed the duration to be broken out by day - if that makes sense.

 

Here is an snippet of the data from the table. Ignore the "min alarm received date" column - that's for another measure.

 

2022-12-23_15-48-19.jpg

 

 

Ok, so... 

splitting_datetime_to_datetime_with_nullend_and_nullstart ^.^

If StartDate is null, all the columns: Start Datetime, End Datetime and Duration will be null

let getParameters = (StartDate, EndDate) =>
    
    let

        NullStart = StartDate = "" or StartDate = null,
        Start = if NullStart then null else Date.From(StartDate),
        NullEnd = EndDate = "" or EndDate = null,
        End = if NullEnd then null else Date.From(EndDate),

        CountDays = if NullEnd or NullStart then 1 else Duration.Days(End-Start) + 1,

        DateList = if NullStart then {null} else List.Dates(Start, CountDays, #duration(1, 0, 0, 0)),

        #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
        #"Add Custom Start DateTime" = Table.AddColumn(#"Converted to Table", "Start datetime", each if NullStart then null else if [Date] = Start then StartDate else DateTime.From([Date])),
        #"Add Custom End DateTime" = Table.AddColumn(#"Add Custom Start DateTime", "End datetime", each if NullEnd then null else if [Date] = End then EndDate else [Date] & #time(23,59,59)),
        #"Change Types" = Table.TransformColumnTypes(#"Add Custom End DateTime",{{"End datetime", type datetime}, {"Start datetime", type datetime}}),
        #"Duration" = Table.AddColumn(#"Change Types", "Duration", each Number.RoundUp(Duration.TotalMinutes([End datetime]-[Start datetime])/60), type number),
        #"Remove Date" = Table.RemoveColumns(#"Duration",{"Date"})
        
    in
        #"Remove Date"
        
in
    getParameters

 





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

Proud to be a Super User!




Okay, this seemed to work. However, I had to remove the following:

  • In the first line I removed "let getParameters"
  • In the last two lines I removed "in getParameters"

Without removing that it did not work. Are those removals going to be a problem?

 

Now the durations appear correctly, however they are whole numbers. 

 

Example: 25 minutes is showing as 1 hour.

 

I would prefer it showed with three decimal places so it would be 0.417. Any idea how to switch this to 3 decimals?

 

Thanks again for all of your help. I really appreciate this.

I think it's because you are adding this as a custom column. Not as a function like I told you.

 

From New Source select Blank Query. Name this query on the left box (like changing the name of the source table). Then open Advanced Editor (it's a button next to the Refresh Preview)and paste this:

let getParameters = (StartDate, EndDate) =>
    
    let

        NullStart = StartDate = "" or StartDate = null,
        Start = if NullStart then null else Date.From(StartDate),
        NullEnd = EndDate = "" or EndDate = null,
        End = if NullEnd then null else Date.From(EndDate),

        CountDays = if NullEnd or NullStart then 1 else Duration.Days(End-Start) + 1,

        DateList = if NullStart then {null} else List.Dates(Start, CountDays, #duration(1, 0, 0, 0)),

        #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
        #"Add Custom Start DateTime" = Table.AddColumn(#"Converted to Table", "Start datetime", each if NullStart then null else if [Date] = Start then StartDate else DateTime.From([Date])),
        #"Add Custom End DateTime" = Table.AddColumn(#"Add Custom Start DateTime", "End datetime", each if NullEnd then null else if [Date] = End then EndDate else [Date] & #time(23,59,59)),
        #"Change Types" = Table.TransformColumnTypes(#"Add Custom End DateTime",{{"End datetime", type datetime}, {"Start datetime", type datetime}}),
        #"Duration" = Table.AddColumn(#"Change Types", "Duration", each Duration.TotalMinutes([End datetime]-[Start datetime])/60, type number),
        #"Remove Date" = Table.RemoveColumns(#"Duration",{"Date"})
        
    in
        #"Remove Date"
        
in
    getParameters

After doing that you should see something like this:

bolfri_0-1671843791189.png

 

Then go to your table and from ribbon select new column > invoke custom function:

bolfri_1-1671843849260.png

Select column name, query name that you have provided in previous steps and the start and end columns (example on screenshot).

bolfri_2-1671843911449.png

And you should know the rest. 🙂





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

Proud to be a Super User!




ShaneL79
Helper I
Helper I

I want to add that in my "Calendar" table, there is a row for every date. I believe I just need to create a column or measure that calculates the time for each day. Maybe...?



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.