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

[ShaneL79]

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

DATE	YEAR	MONTH	NUMBER
11/28/2022	2022	November	11
1/1/2021	2021	January	1
6/25/2022	2022	June	6

Table 2: Data

EVENT ID	START TIME	END TIME	HOURS
1	11/28/2022 16:00	12/8/2022 12:00	236
2	1/1/2021 07:00	1/3/2021 14:00	55
3	6/25/2022 22:00	7/5/2022 08:00	208
4	11/30/2022 12:00

What I would like to see is the following:

Event ID	Start Time	End Time	Year	Month	Hours
1	11/28/2022 16:00	11/30/2022 23:59	2022	November	56
1	12/1/2022 00:00	12/8/2022 12:00	2022	December	180
2	1/1/2021 07:00	1/3/2021 14:00	2021	January	55
3	6/25/2022 22:00	6/30/2022 23:59	2022	June	100
3	7/1/2022 00:00	7/5/2022 08:00	2022	July	104
4	11/30/2022 12:00	11/30/2022 23:59	2022	November	12
4	12/1/2022 00:00		2022	December

Thank you in advance. 

[bolfri]

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.

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.

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

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

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

[ShaneL79]

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?

[bolfri]

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.

[ShaneL79]

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. 

[bolfri]

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?

[ShaneL79]

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.

[bolfri]

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

[ShaneL79]

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.

[bolfri · 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:

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

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

And you should know the rest. 🙂

[ShaneL79]

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...?