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
vyuvaraj
Frequent Visitor

Calculate duration by hour of day

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

 

 

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

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.

 

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

4 REPLIES 4
v-frfei-msft
Community Support
Community Support

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.

 

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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

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

 

 

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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.