Time Duration Calculation issue

[jjones3]

Hello everyone,

I am a brand new Power BI user and I am trying to build a visual based on the duration of certain activities and the hours in which they occured.  For each activity, I have the timestamp in which it began and the duration (in hours) that it lasted. I have data over the course of multiple months and was looking to create a bar chart visual that showed the cumulative duration of activities for each hour of the day (0-24) for the entire period of time.  I initially did this by grouping the durations by the hour in which they started (HOUR(StartTimestamp)).  However, doing this makes it so that an entire activity's duration is assigned to the hour in which it started.  For example, for a 3 hour event that starts at noon, the chart shows 3 activity hours during the 12 o'clock hour, as opposed to one hour each from 12-3.  Is there any way I can manipulate the data or the visual in order to display this?

I'm sorry if my question was vague in any way.  Please let me know if you need more information to answer.

Thanks!

[v-juanli-msft]

Hi @jjones3

Here I make a test

Could you give an example about your data model and visual as you wanted?

Best Regards

Maggie

[jjones3]

Hi @v-juanli-msft

Thanks for the reply!

What I have is about 3 months worth of data, and I want to create is a visual that shows the cumulative time across all activities by the hour of the day the activites were occuring.  The attributes I am using are duration time (which is the number of seconds in numerical format) and the activity timestamp (m/dd/yyyy h:mm:ss) I put and example below of how I would like the final visual to look. The x-axis is currently based off the hour of the timestamp in which each activity began (Using the HOUR() function).

The problem with its current layout is that it attributes the entire duration of an activity to the hour that it began.  For example, an activity that began at 6 AM and lasted 18 hours will show 18 hours for 6 AM, as shown below.

What I would like to do is make it so that an 18 hour activity that begins at 6 AM is spread across all of the hours in which the activity is happening (in other words, an hour at 6, an hour at 7, an hour at 8, etc.).  However, I do not know how to edit the data in order to so.

Thanks again!

[v-juanli-msft]

Hi @jjones3

I don't know exactly your dataset, so I make a test based on my simple data.

If there is something different with your scenorio, please let me know your data structure.

Create calculated columns

lasttime = CALCULATE(MAX([timestamp]),FILTER(ALL(Sheet1),[Index]=EARLIER(Sheet1[Index])-1))

duration = IF([lasttime]<>BLANK(),HOUR([timestamp]-[lasttime]),1)

hourofstart = HOUR([timestamp])

Best regards

Maggie