Time calculations - Hours per month over different periods

[qball]

I have an interesting oen that I don't know how to solve.  

The data looks something like this:

ID          Revision        Start Date  & Time   End Date & Time

1            1                   2016/02/03 14:26    2016/02/06 13:58

1            2                   2016/02/06 13:58    2016/08/03 9:06

2            1                   2016/02/11 15:21    2016/02/11 16:55

2            2                   2016/02/11 16:55    2016/02/12  8:55

2            3                   2016/02/12  8:55     2016/06/29  15:10

2            4                   2016/06/29  15:10   2016/07/05 9:30

2            5                   2016/07/05 9:30      2017/01/03 15:00

The table contains around 150k lines.  

The request is :

• Report total hours that elapsed per ID or ID and Revision in hours (actually working hours but that is not the problem right now)
• Break down those hours per month, even if the start and end date crossed many months, it needs to report the maximum for those months.  

So initial output I would require to get this working is just Month/Year - total hours.  From there I should break it down into per ID etc.

I'm not sure how to implement the monthly calculations?  Can anybody help?

[qball]

Eric - you are a legend!

Thank you so much - all working now.  Also got it to work by calculating the working hours (minus weekends and holidays).

[Eric_Zhang]

Glad to help. 🙂

[Eric_Zhang · Accepted solution]

---

@qball wrote:

I have an interesting oen that I don't know how to solve.  

The data looks something like this:

ID          Revision        Start Date  & Time   End Date & Time

1            1                   2016/02/03 14:26    2016/02/06 13:58

1            2                   2016/02/06 13:58    2016/08/03 9:06

2            1                   2016/02/11 15:21    2016/02/11 16:55

2            2                   2016/02/11 16:55    2016/02/12  8:55

2            3                   2016/02/12  8:55     2016/06/29  15:10

2            4                   2016/06/29  15:10   2016/07/05 9:30

2            5                   2016/07/05 9:30      2017/01/03 15:00

The table contains around 150k lines.  

The request is :

• Report total hours that elapsed per ID or ID and Revision in hours (actually working hours but that is not the problem right now)
• Break down those hours per month, even if the start and end date crossed many months, it needs to report the maximum for those months.  

So initial output I would require to get this working is just Month/Year - total hours.  From there I should break it down into per ID etc.

I'm not sure how to implement the monthly calculations?  Can anybody help?

---

@qball

To achieve the goal, I'll create a calendar table as below

calendar = 
ADDCOLUMNS (
    FILTER ( CALENDAR ( "2016-01-01", "2017-12-31" ), DAY ( [Date] ) = 1 ),
    "MonthEnd", EOMONTH ( [Date], 0 )
)

Then create a calculated table as below, to expand the dates window to multiple rows for each month.

calculated Table = 
FILTER (
    CROSSJOIN ( yourTable, 'calendar' ),
    OR (
        OR (
            yourTable[Start Date  & Time] >= 'calendar'[Date]
                && yourTable[Start Date  & Time] <= 'calendar'[MonthEnd],
            'calendar'[MonthEnd] >= yourTable[Start Date  & Time]
                && 'calendar'[MonthEnd] <= yourTable[End Date & Time]
        ),
        ( yourTable[End Date & Time] ) >= 'calendar'[Date]
            && yourTable[End Date & Time] <= 'calendar'[MonthEnd]
    )
)

Then create a calculated column as

elapsed hours = 
SWITCH (
    TRUE (),
    'calculated Table'[Start Date  & Time] >= 'calculated Table'[Date]
        && 'calculated Table'[End Date & Time] <= 'calculated Table'[MonthEnd], DATEDIFF (
        'calculated Table'[Start Date  & Time],
        'calculated Table'[End Date & Time],
        HOUR
    ),
    'calculated Table'[Start Date  & Time] >= 'calculated Table'[Date]
        && 'calculated Table'[End Date & Time] >= 'calculated Table'[MonthEnd], DATEDIFF (
        'calculated Table'[Start Date  & Time],
        'calculated Table'[MonthEnd],
        HOUR
    ),
    'calculated Table'[Date] > 'calculated Table'[Start Date  & Time]
        && 'calculated Table'[MonthEnd] < 'calculated Table'[End Date & Time], DATEDIFF ( 'calculated Table'[Date], 'calculated Table'[MonthEnd], HOUR ),
    DATEDIFF ( 'calculated Table'[Date], 'calculated Table'[End Date & Time], HOUR )
)

So finally

See more details in the attached pbix file.

[qball]

Thank you Eric - will do it right now and let you know the outcome.