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.
I'm new to Power-Bi and just need help creating a new custom columns.
The one I need help with is:
I have a table already containing;
* Name (over 100 names)
* Start date (when they started)
* Activities (2 different activities, that are named already, that need to be completed)
What I need is a clustered column chart to show:
Across a timeline, how many people (Name) need to complete Activity 1 in 30 days
Across the same timeline how many people need to complete Activity 2 in 7 months
The chart should then show: (for example)
In March there are 3 Activity 1's planned and 10 Activity 2's
In April there are 10 Activity 1's planned and 20 Activity 2's
In May....
In June....
Etc.
The Activities will both need a formula for the dates (30 days for Activity 1 and 7 months for Activity 2)
That is where I get lost with creating this one 😞
Really appreciate any help with this.
Solved! Go to Solution.
Hi @DavidM6051 ,
1. Try to add a new custom column in power query:
if [Activity Name] = "2.4 -Session 1" then Date.AddMonths([Start Date],1) else if [Activity Name] = "9.8 - Final session" then Date.AddMonths([Start Date],7) else null
2. Create a disconnected calendar table
Table 2 = CALENDAR(MIN('Table'[Start Date]),MAX('Table'[End Date]))
3. Create a measure like below:
A =
CALCULATE(COUNTX(FILTER('Table',[Start Date]<=MAX('Table 2'[Date])
&&'Table'[End Date]>MAX('Table 2'[Date])),('Table'[Name])))
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@DavidM6051 ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Name | Start Date | Activity Name |
A | 29/06/2021 | 2.4 -Session 1 |
A | 29/06/2021 | 9.8 - Final session |
B | 29/06/2021 | 2.4 -Session 1 |
B | 29/06/2021 | 9.8 - Final session |
C | 29/06/2021 | 2.4 -Session 1 |
C | 29/06/2021 | 9.8 - Final session |
D | 29/06/2021 | 2.4 -Session 1 |
D | 29/06/2021 | 9.8 - Final session |
E | 29/06/2021 | 2.4 -Session 1 |
F | 29/06/2021 | 2.4 -Session 1 |
G | 29/06/2021 | 2.4 -Session 1 |
G | 29/06/2021 | 9.8 - Final session |
H | 23/09/2021 | 9.8 - Final session |
I | 23/09/2021 | 9.8 - Final session |
L | 23/09/2021 | 2.4 -Session 1 |
M | 23/09/2021 | 2.4 -Session 1 |
J | 19/10/2021 | 2.4 -Session 1 |
J | 19/10/2021 | 9.8 - Final session |
N | 19/10/2021 | 2.4 -Session 1 |
O | 19/10/2021 | 2.4 -Session 1 |
P | 19/10/2021 | 2.4 -Session 1 |
K | 23/11/2021 | 2.4 -Session 1 |
Test Tester | 23/11/2021 | 2.4 -Session 1 |
Test Tester | 23/11/2021 | 9.8 - Final session |
So as you can see @amitchandak some people have 1 activity (session 1 or final session) and some have both (session 1 and final session).
If session 1 needs to be completed by end of month 1 and the final session needs to be completed by month 7... how do I visualize this in a timeline chart?
I'm guessing that I would need a formla in a new column to add the dates?
Hi @DavidM6051 ,
1. Try to add a new custom column in power query:
if [Activity Name] = "2.4 -Session 1" then Date.AddMonths([Start Date],1) else if [Activity Name] = "9.8 - Final session" then Date.AddMonths([Start Date],7) else null
2. Create a disconnected calendar table
Table 2 = CALENDAR(MIN('Table'[Start Date]),MAX('Table'[End Date]))
3. Create a measure like below:
A =
CALCULATE(COUNTX(FILTER('Table',[Start Date]<=MAX('Table 2'[Date])
&&'Table'[End Date]>MAX('Table 2'[Date])),('Table'[Name])))
Best Regards,
Liang
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 |
---|---|
100 | |
99 | |
76 | |
66 | |
61 |
User | Count |
---|---|
142 | |
106 | |
103 | |
85 | |
70 |