Forum Discussion
Power-Bi with a data-source (DWH)
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.
Hi Anonymous ,
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 null2. 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.
5 Replies
Anonymous ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.Thanks amitchandak is the sample data okay in a CSV file format?
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
