Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I'm trying to work out how I can calclate the amount of time that has not been submitted by a resource to date. I already have a few elements:
Total amount workable days to date in this calendar month
Total amount of submitted days in this calendar week
The Calender week is house in Timecard Split which links to timecard via the Unique ID, Timecard links to the resource via the resource ID and resource links to the Utilisation Detail object via the resource ID as well (all relationships are bi-directional).
If I try to create a column in Timecard Split to pull in the number of workable days to date in this calendar month it just doesn't give me the option & therefore I cannot minus one from the other in the timecard split object...
Any one else had some luck with the above?
Thanks
Can you supply some sample data so that we can model it?
Just reliased i can copy and paste here so please find below a small subset of data as an illustrative example (can't give to much more just as company data etc)
Please also find the Relationship map attached.
Looking forwards to your thoughts.
Thanks
Timecard Split:
Resource | Total Days Worked | Start Date | Timecard: Timecard Id | Timecard Split: Timecard Split Id |
AAA | 0.75 | 06/02/2017 | TCH-02-10-2017-008153 | TC-02-10-2017-009311 |
AAA | 3.25 | 06/02/2017 | TCH-02-10-2017-008152 | TC-02-10-2017-009312 |
Time Card:
Resource | Total Days Worked | Start Date | Timecard: Timecard Id |
AAA | 3.25 | 06/02/2017 | TCH-02-10-2017-008152 |
AAA | 0.75 | 06/02/2017 | TCH-02-10-2017-008153 |
Contact :
Resource |
AAA |
Utilisation Detail:
Resource | Historical Calendar Hours | Time Period | Utilization Calculation: Utilization Calculation Name | Utilization Detail: Utilization Detail Id | |
AAA | 160 | Feb-17 | Global Utilization Calculation | UD170783 |
|
This is all in the Force.com platform.
The relationship map is as follows:
Timrcard split ID <-> ID Timecard
Timecard - Resource <-> ContactID
ContactID <-> Utilisation Detail - Resource
Hi @Voose,
Regarding the sample data, what's the desired results you want? Can you elaborate "the amount of time that has not been submitted by a resource to date"?
Best Regards,
Qiuyun Yu
Hello again @v-qiuyu-msft!
Realise my previous description was poor, what I would like to do is as follows, I have a bunh of names and next to them their timecards and if they are approved /saved / submitted and a total as shown below:
I want to do a few things:
Firstly - Create a column that I can display within this table that shows the amount of working calendar days past so far in the current month upto the last friday (if the month starts on a wednesday like March then i want Wed - friday included) for exmaple for day of posting this I would want the column to = 8.
Things to note - The above timecard information is not stored in 1 row in the underlying data as the resources may store their time in small chunks. For example the first row of data shows 8 days approved, this could be made up of 8 X16 4 hour rows as you can see later on in the table it starts to give some odd numbers.
Once the above is done I would then like to create a to be saved column, to be submitted and to be approved. these are farily basic columns:
A total days so far | B approved | C saved | D sub | E to be saved | F to be submitted | G to be approved |
8 | 8 | 5 | 0 | -5 | 0 | 0 |
8 | 3 | 0 | 0 | 5 | 0 | 0 |
8 | 3 | 0 | 5 | 0 | 0 | 5 |
8 | 3 | 0 | 0 | 5 | 5 | 0 |
Assuming the far left one is col A the formulae in excel would be as follows:
To be saved = =A33-(B33+C33+D33)
To be submitted = =A33-(B33+D33)
To be approved = =D33
Looking forward to your thougts!
Thanks
Still struggling with this one chaps have been trying to think of a solution here and have come up with one that is plausible just unsure how to build it into BI.
Idea being that I could create a calculated table for each status of time card I.E. create an approved table that shows only approved timecards for each resource and summarises all of the hours / timecards for one person for a particular day.
Hope someone can help me here - struggling with this one 😞
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |