How to properly format project date table (start/stop) and use for visuals, e.g. "as timeline"

[Anonymous]

Hi,

I have what seems like a very basic issue, however I'd like to get the understanding on how to do this correctly.

I have a table with projects, activities, start and stop dates for those. As I have learned, the proper way to organize data is to have "tidy", i.e. one observation per row. There for I have unpivoted the start and stop dates. This however results in that some visuals such the custom visual "as timeline" is not working properly when I try to create measures for the start and stop date, even though I can see the same format if I add the measures to a table visual.  

Data format "non tidy":

Project ID	Activity	Start	Stop
Project 1	Activity 1	2020-01-01	2020-01-03
Project 1	Activity 2	2020-01-03	2020-01-15
Project 1	Activity 3	2020-01-16	2020-01-30
Project 2	Activity 1	2020-02-01	2020-02-03
Project 2	Activity 2	2020-02-03	2020-02-15
Project 2	Activity 3	2020-02-16	2020-02-27
Project 3	Activity 1	2020-03-01	2020-03-03
Project 3	Activity 2	2020-03-03	2020-03-15
Project 3	Activity 3	2020-03-16	2020-03-30

Data format "tidy":

Project ID	Activity	Start/Stop	Date
Project 1	Activity 1	Start	2020-01-01
Project 1	Activity 1	Stop	2020-01-03
Project 2	Activity 1	Start	2020-02-01
Project 2	Activity 1	Stop	2020-02-03
Project 3	Activity 1	Start	2020-03-01
Project 3	Activity 1	Stop	2020-03-03
Project 1	Activity 2	Start	2020-01-03
Project 1	Activity 2	Stop	2020-01-15
Project 2	Activity 2	Start	2020-02-03
Project 2	Activity 2	Stop	2020-02-15
Project 3	Activity 2	Start	2020-03-03
Project 3	Activity 2	Stop	2020-03-15
Project 1	Activity 3	Start	2020-01-16
Project 1	Activity 3	Stop	2020-01-30
Project 2	Activity 3	Start	2020-02-16
Project 2	Activity 3	Stop	2020-02-27
Project 3	Activity 3	Start	2020-03-16
Project 3	Activity 3	Stop	2020-03-30

Measure sample:

Start MAX = CALCULATE(MAX('SampleData (Tidy)'[Date]),'SampleData (Tidy)'[Start/Stop]="Start")

[Anonymous]

Hi @Anonymous ,

Could you please provide your desired result with screenshot? Please check if the below screenshot is what you want. If yes, you can refer the following documentations to create Gantt visual.

Power BI Custom Visuals - Gantt

Gantt Chart- Advanced Custom Visuals for Power BI

Best Regards

Rena

[Anonymous]

Thank for your reply!

I have no issue getting gant visuals to work with the table format you have presented in the image. See similar result on my end in this screenshot:

Output visual with non tidy data

However, it seems from a data modelling view better to format my table as an event table with only one event per row (and not two columns with date). This would help for instance in displaying last 10 events and such, independent if its a start event or stop event. Take a look at this screen: 

Output tidy data with measure

The table with measures is precisly as the in the first screen but this does not work for the visual. I'm assuming it might be the measure that have not been set up correctly to achieve this but I am not certain. 

[Anonymous]

Hi @Anonymous ,

Please keep the format of data as "non tidy" and drag the related fields onto as Timeline visual according to the below settings:

Fields pane setting
Entity:	Project ID
Category:
Start Date:	Start
End Date:	Stop
Tooltip texts:
Overlay:	Activity

If it is not working, please provide your desired result and more details on it. Thank you.

Best Regards

Rena

[Anonymous]

And if I would like to achieve the same result with "tidy data", i.e. one column with dates, how would I creates such a "start measure" and "stop measure"?