Forum Discussion

ShaneL79's avatar
ShaneL79
Helper I
3 years ago
Solved

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 

 

 

Thank you in advance. 

Need Help
  • bolfri's avatar
    bolfri
    3 years ago

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

10 Replies

  • bolfri's avatar
    bolfri
    Solution Sage
    3 years ago

    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's avatar
      ShaneL79
      Helper I
      3 years ago

      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's avatar
        bolfri
        Solution Sage
        3 years ago

        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's avatar
    ShaneL79
    Helper I
    3 years ago

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