cancel
Showing results for
Did you mean:
qball 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 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 =
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.

4 REPLIES 4 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 =
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 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

## 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). Eric_Zhang
Moderator

## Re: Time calculations - Hours per month over different periods

Announcements #### 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. #### Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones. #### 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

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI. Top Kudoed Authors
Users Online
Currently online: 360 members 3,261 guests
Recent signins:
• ejpbiuser • powerbitag • prmpn • connortapp • eranatatera • josemanuelcayon • saviourofdp • rcoombe3 • GAMUS • Dalex33 • harish9211 • Alex_W_CH • Dragonfly • Guillaume_PSA 