Calculate Timesheet Hours (duration) and display by month using stacked column graph

[Anonymous]

Hi,

I'm working with timesheet data pulled from Clockify.

They already push through the "Interval duration", which is the employees daily time captured in hh:nn:ss. So we don't have to work that out the duration with the start and end times.

My data looks as follows:

Name of file: "Clockify Time Entries"

Workspace ID	User ID	Time Entry ID	Billable	Project ID	Interval Start	Interval End	Interval Duration	Interval Start Date	Interval End Date
hhgg66rety78iop0kjhgf456	12ff456hhy678hftr45632rf	7676790oi08df432f654fx23	TRUE	654thgfre65342fdhu874tge	2022-09-06T07:00:00Z	2022-09-06T15:00:00Z	08:00:00	2022-09-06	2022-09-06
hhgg66rety78iop0kjhgf456	12ff456hhy678hftr45632rf	7676790oi08df432f6hgfd54	FALSE	654thgfre65342fdhu874tge	2022-04-28T06:10:08Z	2022-04-28T14:10:08Z	08:00:00	2022-04-28	2022-04-28

- I'm aiming to use the "Interval Duration" and "Interval Start Date" columns

- Interval Duration format: Set to "Duration" type, and format (hh:nn:ss)

What I would like to achieve:

- Display the sum of the duration/ time captured per month

- This will then allow you to filter by Project/client/users to see more details on the sum of hours logged

Issue I'm facing:

- Seems like Power BI does not want to sum my "Interval Duration" column

- I selected a stacked column graph, then on X-Axis = Interval start date to get Year and Months, and on the Y-Axis = Count of Interval Duration

- The issue is for some reason it's counting the amount of rows, and not the actual Interval Duration for each row

- i.e. for the month of October it counted 35 lines of timesheet entries 😞 but i'm looking for the sum of the duratiuns captured for October

here is my current visual:

I face the same issue when trying to display the count of Interval duration by Employee.

- Again power BI thinks i'm looking for the rows of entries, and not counting the actual duration they've enetered

Does anyone know how to tell the query to SUM the duration values?

[Anonymous]

ppm1 That worked. I've ended up adding an additional column, converting my duration to Hours.. and the graphs are working now. Thanks for the support

[Anonymous]

Hi ppm1, thank you. Will give that a try

[ppm1 · Accepted solution]

Please see this article and video. It is best to format your durations as a decimal (in days), do your calculations, and then format it at the end to display it in the format you'd like.

Calculate and Format Durations in DAX – Hoosier BI

Pat