Forum Discussion
Power-Bi with a data-source (DWH)
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.
| 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 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.