Spreading the Value from Start date to end date by Weekly

[krrish116]

Hi @amitchandak,

I have 2 tables

Allocation ; Requests

In Allocation

A-ID	A-StartDate	A-EndDate	A-Hours
1	8/24/2020	12/31/2021	40
4	8/27/2020	12/31/2021	0.5
5	8/31/2020	12/31/2021	40
6	10/5/2020	12/31/2021	30
7	8/31/2020	12/31/2021	8
8	12/7/2020	3/19/2021	15
46	9/14/2020	1/29/2021	4
44	12/7/2020	12/31/2021	30
43	12/21/2020	12/31/2021	5
39	12/7/2020	12/31/2021	5
37	12/2/2020	1/31/2021	2
36	11/25/2020	12/7/2020	8
36	11/25/2020	12/7/2020	2
35	11/11/2020	12/11/2020	10
35	11/11/2020	12/11/2020	15

In Requests

Task	AssignedTo	Hours	R-Start Date	ID	R-End Date
Repetitive	John	40	8/24/2020	1	12/31/2021
Repetitive	Peter	5	3/26/2021	4	12/31/2021
Project		30	9/4/2020	5	10/14/2020
Repetitive	Zeus	0.5	8/24/2020	6	12/31/2021
Repetitive	John	60	8/31/2020	7	12/31/2021
Repetitive	Barang	30	10/5/2020	8	12/31/2021
One-time	Narod	1	10/13/2020	46	1/31/2021
One-time			11/2/2020	44	12/15/2020
One-time	Zeus	20	9/16/2020	43	12/15/2020
One-time			8/10/2020	39	11/18/2020
One-time			8/13/2020	37	10/23/2020
Project	Barang	30	9/4/2020	36	10/14/2020
Project	Narod	16	11/23/2020	36	3/26/2021
Project	John	5	7/6/2020	35	11/10/2020
Project	Peter	6	11/6/2020	32	1/31/2021

So, based on the above tables the Relationship is A-ID ->ID

Now i have to show a graph based on the above tables.

in that graph the dates are shown on weekly basis and week starts with Monday.

I have to calculate the sum of A-Hours for A-ID and TASK "Repetitive". From start to end date i have to show 

For Example :

A-ID 1 has 40 Hrs from  8/24/2020 to 12/31/2021

so for all these dates it has to show 40 in all bars like wise for

A-ID  4 has 0.5 Hrs from  8/27/2020 to 12/31/2021

so for all these dates it has to show 0.5 in all bars like wise for

So, i want to know how should i show and distribute the value to all dates in graph..

Thanks,

Krish.

[v-yuaj-msft · Accepted solution]

Hi @krrish116 ,

Based on your description, you can do some steps as follows.

1. create a calendar table.

Calendar =
ADDCOLUMNS (
CALENDAR (
CALCULATE ( MIN ( 'Allocation'[A-StartDate] ), ALL () ),
CALCULATE ( MAX ( 'Allocation'[A-EndDate] ), ALL () )
),
"Yesr_week",
YEAR ( [Date] ) & "-"
& WEEKNUM ( [Date], 2 )
)

2. create two columns in "Calendar" table.

AID_Repetitive_sum =
VAR x1 =
FILTER (
ADDCOLUMNS (
'Allocation',
"task", LOOKUPVALUE ( Requests[Task], Requests[ID], [A-ID] )
),
[task] = "Repetitive"
)
RETURN
SUMX (
FILTER ( x1, [Date] >= [A-StartDate] && [Date] <= [A-EndDate] ),
[A-Hours]
)

 X_axis_Rank =
RANKX (
ALL ( 'Calendar' ),
VALUE ( LEFT ( [Yesr_week], 4 ) ) * 1000
+ WEEKNUM ( [Date], 2 ),
,
ASC
)

3.Create a line chart.

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.