Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
Eric_Zhang
Employee
Employee


@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
qball
Frequent Visitor

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

Glad to help. 🙂

Eric_Zhang
Employee
Employee


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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.