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

Aggregate Timesheets by week to compare against scheduled hours

Hi All

 

I've tried to do this numerous ways (M Query using table Merges and indexes, and DAX using SUMMARIZE) and come up short.

 

I have two tables, and have cut this list down to the relevant markers (the UserName, ProjectName and ClientName exists but isn't relevant)

Timesheets

UserIDProjectIDClientIDCommentDateHours
111Wrote Proposal27 March 20230.5
122Drafted Memo28 March 20220.9
232Meeting28 March 20232
211Prepared for Meeting29 March 20231
232Edited Document29 March 20230.5
332Called Client30 March 20233
332Meeting30 March 20235

 

Scheduled Hours

 

UserIDProjectIDClientIDWeekStartHours
11127 March 20235
22227 March 20235
33227 March 20235

 

What I need to do is have the actual hours schedule against the aggregate. In this case it would look like this:

 

UserIDProjectIDClientIDWeekStartScheduled HoursActual
11127 March 202350.5
22227 March 202350.0
33227 March 202358
12227 March 202300.9
23227 March 202302.5
21127 March 202301

 

Can anyone help please?

3 REPLIES 3
Ahmedx
Super User
Super User

if that gives you the correct result, then the index will be the correct solution to the duplication problem

Ahmedx
Super User
Super User

watch my video
https://1drv.ms/v/s!AiUZ0Ws7G26Rhw1csRNGZk9XmquL?e=pDz07f

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26Rhwx7s0biyiBlfb0l?e=coFhpr

Hey @Ahmedx ,

 

Thank you so much for your prompt solution!

Regarding the above, I've found it caused a masssive duplication (several million) of records so I created an index before I filtered which seemed to bring the records down to a level I'd expect (There are 210,000 Timesheet records and 200,000 Resourcing rows, and the aggregated records are around 180,000). 

Would the index be the correct solution to the duplication problem?

DataSaurus_0-1681174652529.png

 


Many thanks

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.