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
Anonymous
Not applicable

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

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 IDUser IDTime Entry IDBillableProject IDInterval StartInterval EndInterval DurationInterval Start DateInterval End Date
hhgg66rety78iop0kjhgf45612ff456hhy678hftr45632rf7676790oi08df432f654fx23TRUE654thgfre65342fdhu874tge2022-09-06T07:00:00Z2022-09-06T15:00:00Z08:00:002022-09-062022-09-06
hhgg66rety78iop0kjhgf45612ff456hhy678hftr45632rf7676790oi08df432f6hgfd54FALSE654thgfre65342fdhu874tge2022-04-28T06:10:08Z2022-04-28T14:10:08Z08:00:002022-04-282022-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:

Screenshot 2022-11-10 at 11.34.25.png

 

 

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?

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

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

Microsoft Employee

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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
Not applicable

Hi ppm1, thank you. Will give that a try

ppm1
Solution Sage
Solution Sage

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

Microsoft Employee

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.

Top Solution Authors
Top Kudoed Authors