Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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) * A...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    4 years ago

    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 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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
V-lianl-msft
Community Support
4 years ago

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 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.