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 @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.
Solved! Go to 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.
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.
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |