cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
qball Frequent Visitor
Frequent Visitor

Time calculations - Hours per month over different periods

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?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: Time calculations - Hours per month over different periods


@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 )
)

Capture.PNG

 

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]
    )
)

Capture.PNG

 

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

Capture.PNG

 

See more details in the attached pbix file.

View solution in original post

4 REPLIES 4
Moderator Eric_Zhang
Moderator

Re: Time calculations - Hours per month over different periods


@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 )
)

Capture.PNG

 

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]
    )
)

Capture.PNG

 

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

Capture.PNG

 

See more details in the attached pbix file.

View solution in original post

qball Frequent Visitor
Frequent Visitor

Re: Time calculations - Hours per month over different periods

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

qball Frequent Visitor
Frequent Visitor

Re: Time calculations - Hours per month over different periods

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

Moderator Eric_Zhang
Moderator

Re: Time calculations - Hours per month over different periods

Glad to help. 🙂

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 360 members 3,261 guests
Please welcome our newest community members: