topic Re: If date between project date ranges return value, if outside date range return ZERO in DAX Commands and Tips

If date between project date ranges return value, if outside date range return ZERO

Anonymous — Fri, 13 Oct 2023 08:53:25 GMT

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...

Calculate (VALUES ('RPPlan'[Hrs Per Week]), FILTER ( ALL( 'WeekEndDate') , ('WeekEndDate'[WeekEndDate] > MIN('RPPlan'[StartDate]) && 'WeekEndDate'[WeekEndDate] <= MAX('RPPlan'[WeekEndDate]))))

The project date range in the data on each row can be anything from 1 day to 1 year.

Any help appreciated

thanks

Re: If date between project date ranges return value, if outside date range return ZERO

lbendlin — Sat, 14 Oct 2023 12:01:56 GMT

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

Re: If date between project date ranges return value, if outside date range return ZERO

Anonymous — Mon, 16 Oct 2023 08:41:32 GMT

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

Re: If date between project date ranges return value, if outside date range return ZERO

Anonymous — Mon, 16 Oct 2023 08:41:56 GMT

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

Re: If date between project date ranges return value, if outside date range return ZERO

Anonymous — Mon, 16 Oct 2023 08:42:29 GMT

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

Re: If date between project date ranges return value, if outside date range return ZERO

Anonymous — Mon, 16 Oct 2023 08:43:13 GMT

The first table is the data

The Second Table is the calendar Table

The third table is the expected result

ANy help greatly appreciated

Re: If date between project date ranges return value, if outside date range return ZERO

Anonymous — Mon, 16 Oct 2023 08:43:37 GMT

Expected Result

Re: If date between project date ranges return value, if outside date range return ZERO

Anonymous — Mon, 16 Oct 2023 08:43:48 GMT

Base Data

Re: If date between project date ranges return value, if outside date range return ZERO

lbendlin — Mon, 16 Oct 2023 20:51:33 GMT

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?

Re: If date between project date ranges return value, if outside date range return ZERO

Anonymous — Tue, 17 Oct 2023 08:10:31 GMT

Working Days are Monday to Friday only.. thanks 🙂

Re: If date between project date ranges return value, if outside date range return ZERO

lbendlin — Tue, 17 Oct 2023 12:56:48 GMT

BADGER has 22 working days, not 21.