Power-Bi with a data-source (DWH)

[DavidM6051]

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.

[amitchandak]

@DavidM6051 ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[DavidM6051]

Thanks @amitchandak is the sample data okay in a CSV file format?

[DavidM6051]

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

[DavidM6051]

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?

[V-lianl-msft · Accepted 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.