Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I am currently desiging a project report and have a problem getting the right figures in each column for weekly figures
For Example, this is how the data is stored
Peter Smith (Project Dates) 1st Nov to 30th November (Project hours) 20hrs
I need a table that pro ratas the 22hrs over the number of weeks in November
So it should look like this in the columns
Peter Smith (WE 5th Nov) 5hrs, (WE12th) 5hrs, (WE 19th) 5hrs, (WE 26th) 5hrs = Total 20hrs
I can get the DAX to show the 5hrs in the final week but not in the previous weeks...
Solved! Go to Solution.
The first table is the data
The Second Table is the calendar Table
The third table is the expected result
ANy help greatly appreciated
PROJECT | NAME | Start Date | End Date | Hours Assigned | Working Days in Period | Hrs Per Day | 17/09/2023 | 24/09/2023 | 01/10/2023 | 08/10/2023 | 15/10/2023 | 22/10/2023 | 29/10/2023 | 05/11/2023 | 12/11/2023 | 19/11/2023 | 26/11/2023 | 03/12/2023 | TOTAL |
KANGAROO | Peter Smith | 16//09/2023 | 31/10/2023 | 50 | 32 | 1.563 | 3.125 | 7.8125 | 7.8125 | 7.8125 | 7.8125 | 7.8125 | 7.8125 | 0 | 0 | 0 | 0 | 0 | 50 |
BADGER | John Doe | 01/10/2023 | 31/10/2023 | 22 | 20 | 1.100 | 0 | 0 | 0 | 5.5 | 5.5 | 5.5 | 5.5 | 0 | 0 | 0 | 0 | 0 | 22 |
LION | Jane Taylor | 01/10/2023 | 07/10/2023 | 40 | 5 | 8.000 | 0 | 0 | 0 | 40 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 40 |
TIGER | Lou Reed | 01/11/2023 | 30/11/2023 | 176 | 22 | 8.000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 24 | 40 | 40 | 40 | 32 | 176 |
3.125 | 7.8125 | 7.8125 | 53.3125 | 13.3125 | 13.3125 | 13.3125 | 24 | 40 | 40 | 40 | 32 | 288 |
Expected Result
PROJECT | NAME | Start Date | End Date | Hours Assigned | Working Days in Period | Hrs Per Day |
KANGAROO | Peter Smith | 16//09/2023 | 31/10/2023 | 50 | 32 | 1.563 |
BADGER | John Doe | 01/10/2023 | 31/10/2023 | 22 | 21 | 1.048 |
LION | Jane Taylor | 01/10/2023 | 07/10/2023 | 40 | 5 | 8.000 |
TIGER | Lou Reed | 01/11/2023 | 30/11/2023 | 176 | 22 | 8.000 |
Base Data
Thank you for the sample data. Next you need to indicate what you mean by 5 working days. For example 2023-09-16 was a saturday. Is that considered to be a working day? or should it be ignored? Do you want to consider holidays?
Working Days are Monday to Friday only.. thanks 🙂
End Date | Week Ending Date | Working Days |
16/09/2023 | 17/09/2023 | 5 |
17/09/2023 | 17/09/2023 | 5 |
18/09/2023 | 24/09/2023 | 5 |
19/09/2023 | 24/09/2023 | 5 |
20/09/2023 | 24/09/2023 | 5 |
21/09/2023 | 24/09/2023 | 5 |
22/09/2023 | 24/09/2023 | 5 |
23/09/2023 | 24/09/2023 | 5 |
24/09/2023 | 24/09/2023 | 5 |
25/09/2023 | 01/10/2023 | 5 |
26/09/2023 | 01/10/2023 | 5 |
27/09/2023 | 01/10/2023 | 5 |
28/09/2023 | 01/10/2023 | 5 |
29/09/2023 | 01/10/2023 | 5 |
30/09/2023 | 01/10/2023 | 5 |
01/10/2023 | 01/10/2023 | 5 |
02/10/2023 | 08/10/2023 | 5 |
03/10/2023 | 08/10/2023 | 5 |
04/10/2023 | 08/10/2023 | 5 |
05/10/2023 | 08/10/2023 | 5 |
06/10/2023 | 08/10/2023 | 5 |
07/10/2023 | 08/10/2023 | 5 |
08/10/2023 | 08/10/2023 | 5 |
09/10/2023 | 15/10/2023 | 5 |
10/10/2023 | 15/10/2023 | 5 |
11/10/2023 | 15/10/2023 | 5 |
12/10/2023 | 15/10/2023 | 5 |
13/10/2023 | 15/10/2023 | 5 |
14/10/2023 | 15/10/2023 | 5 |
15/10/2023 | 15/10/2023 | 5 |
16/10/2023 | 22/10/2023 | 5 |
17/10/2023 | 22/10/2023 | 5 |
18/10/2023 | 22/10/2023 | 5 |
19/10/2023 | 22/10/2023 | 5 |
20/10/2023 | 22/10/2023 | 5 |
21/10/2023 | 22/10/2023 | 5 |
22/10/2023 | 22/10/2023 | 5 |
23/10/2023 | 29/10/2023 | 5 |
24/10/2023 | 29/10/2023 | 5 |
25/10/2023 | 29/10/2023 | 5 |
26/10/2023 | 29/10/2023 | 5 |
27/10/2023 | 29/10/2023 | 5 |
28/10/2023 | 29/10/2023 | 5 |
29/10/2023 | 29/10/2023 | 5 |
30/10/2023 | 05/11/2023 | 5 |
31/10/2023 | 05/11/2023 | 5 |
01/11/2023 | 05/11/2023 | 5 |
02/11/2023 | 05/11/2023 | 5 |
03/11/2023 | 05/11/2023 | 5 |
04/11/2023 | 05/11/2023 | 5 |
05/11/2023 | 05/11/2023 | 5 |
06/11/2023 | 12/11/2023 | 5 |
07/11/2023 | 12/11/2023 | 5 |
08/11/2023 | 12/11/2023 | 5 |
09/11/2023 | 12/11/2023 | 5 |
10/11/2023 | 12/11/2023 | 5 |
11/11/2023 | 12/11/2023 | 5 |
12/11/2023 | 12/11/2023 | 5 |
13/11/2023 | 19/11/2023 | 5 |
14/11/2023 | 19/11/2023 | 5 |
15/11/2023 | 19/11/2023 | 5 |
16/11/2023 | 19/11/2023 | 5 |
17/11/2023 | 19/11/2023 | 5 |
18/11/2023 | 19/11/2023 | 5 |
19/11/2023 | 19/11/2023 | 5 |
20/11/2023 | 26/11/2023 | 5 |
21/11/2023 | 26/11/2023 | 5 |
22/11/2023 | 26/11/2023 | 5 |
23/11/2023 | 26/11/2023 | 5 |
24/11/2023 | 26/11/2023 | 5 |
25/11/2023 | 26/11/2023 | 5 |
26/11/2023 | 26/11/2023 | 5 |
27/11/2023 | 03/12/2023 | 5 |
28/11/2023 | 03/12/2023 | 5 |
29/11/2023 | 03/12/2023 | 5 |
30/11/2023 | 03/12/2023 | 5 |
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
User | Count |
---|---|
54 | |
47 | |
19 | |
16 | |
15 |
User | Count |
---|---|
114 | |
44 | |
44 | |
28 | |
22 |