Calculate duration by hour of day

[vyuvaraj]

I have a columns Start time,                        Stop time                        Location

                            6/14/2018 8:26:00 PM     6/15/2018 5:53:00 AM    IL

The total duration is 567 minutes 

I need to split duration in to hourly basis.The output I need is like below

Date                 Location                1   2   3   4   5  6  7  8  9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24                        

6/14/2018         IL                          0   0    0   0   0  0  0 0  0   0    0    0    0    0    0   0     0    0    0   34  60  60  60  60    

6/15/2018         IL                         60 60  60 60 53 0  0  0  0  0    0    0    0     0    0    0    0    0   0    0    0     0    0    0 

I want to plot a graph with 

Date on x axis and hour of the day on y axis.

Any help is appreciated!

thanks

[v-frfei-msft · Accepted solution]

Hi @vyuvaraj,

Please check the following steps as below.

1. Enter a time table from 00:00:00-23:00:00.

2. Create a calculated table using the formula.

Datetime = ADDCOLUMNS(CROSSJOIN(CALENDAR(MAX(Table1[Start time]),MAX(Table1[Stop time])),'Time'),"datetime",[Date]+'Time'[Time])

3. Then we can create some calcualted columns to meet your requirement.

Hour = HOUR(Datetime[Time])

newdatetime = IF(Datetime[datetime]>=MAX(Table1[Start time]) && Datetime[datetime]<=MAX(Table1[Stop time]),'Datetime'[datetime])

Column = var durs = DATEDIFF(MAX(Table1[Start time]),Datetime[newdatetime],MINUTE)
var dure =DATEDIFF(Datetime[newdatetime],MAX(Table1[Stop time]),MINUTE)
var mind = CALCULATE(MIN(Datetime[newdatetime]),ALL(Datetime))
var maxd = CALCULATE(MAX(Datetime[newdatetime]),ALL(Datetime))
return
IF(ISBLANK(Datetime[newdatetime]),BLANK(),IF(Datetime[newdatetime]=mind,durs,IF(Datetime[newdatetime]=maxd,dure,60)))

D = var datestart = DATE(YEAR(MAX(Table1[Start time])),MONTH(MAX(Table1[Start time])),DAY(MAX(Table1[Start time])))
var dateend = DATE(YEAR(MAX(Table1[Stop time])),MONTH(MAX(Table1[Stop time])),DAY(MAX(Table1[Stop time])))
var dates = DATE(YEAR(Datetime[newdatetime]),MONTH(Datetime[newdatetime]),DAY(Datetime[newdatetime]))
return
IF(dates=datestart,MAX(Table1[Start time]),IF(dates=dateend,MAX(Table1[Stop time])))

4. Then we can get the result as below.

For more details, please check the pbix as attached.

Regards,

Frank

[jcarville]

@v-frfei-msft  this is similar to an issue I'm facing. Your solution makes sense and is the answer to the original query, but what would you change if there were multiple Locations? I have provided a sample data set below:

Start time,                        Stop time                        Location               Value

6/14/2018 8:26:00 PM     6/15/2018 5:53:00 AM    IL                          567

6/15/2018 9:00:00 PM     6/16/2018 2:00:00 AM    MC                       500

6/16/2018 2:00:00 PM     6/16/2018 3:30:00 PM    DE                        150

[vyuvaraj]

I have a columns Start time,                        Stop time                        Location

                            6/14/2018 8:26:00 PM     6/15/2018 5:53:00 AM    IL

The total duration is 567 minutes 

I need to split duration in to hourly basis.The output I need is like below

Date                 Location                1   2   3   4   5  6  7  8  9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24                        

6/14/2018         IL                          0   0    0   0   0  0  0 0  0   0    0    0    0    0    0   0     0    0    0   34  60  60  60  60    

6/15/2018         IL                         60 60  60 60 53 0  0  0  0  0    0    0    0     0    0    0    0    0   0    0    0     0    0    0  

I want to plot a graph with 

Date on x axis and hour of the day on y axis.

Any help is appreciated!

thanks

[v-frfei-msft]

Hi @vyuvaraj,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank