Forum Discussion
If date between project date ranges return value, if outside date range return ZERO
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...
BADGER has 22 working days, not 21.
10 Replies
- lbendlin
Super User
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-Forum/ba-p/963216
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/1447523 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 - lbendlin
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?
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