Chart Bar graph with a project start date and end date

[franciscogsp]

Hello,

I've been trying to solve this challenge for a while and I can't.

I need to present in a bar chart, or similar, the start dates and end dates of this client's projects. The problem is that I have this information in the same row of the table and when I try to add the fields in the chart, they don't match.

Table example:

And in the chart, when I add the date fields in the Y-axis and the count is done, I don't have a "valid" field to add in the X-axis. In X-axis I have added the years either from StartDate or EndDate, but the problem is that in EndDate, for example, the first available year is 2020 and in StartDate is 2015.

I solved the problem by creating a calendar table and duplicating this table from the contracts, one for StartDate and one for EndDate, the problem was that then when clicking on the bars for each year, there was no connection to the model and the remaining charts did not apply the filters.

Can someone help me?

Thanks

[franciscogsp]

The end result I hope, is something similar or the same as this image:

The problem is that to get to this view I had to do this, and not connected to the rest of the model.

Thanks

[Anonymous · Accepted solution]

Hi @franciscogsp ,

Please new 2 measures:

Count of startdate = COUNTROWS('Table')

Count of enddate = CALCULATE(COUNTROWS('Table'),USERELATIONSHIP('Table'[EndDate],'Calendar'[Date]))

and a calendar table:

Calendar = CALENDAR(MIN('Table'[StartDate]),MAX('Table'[EndDate]))

relationships:

result:

The pbix file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[franciscogsp]

Thank you very much for your help, but I have a doubt after having tested this solution.

Imagining that there are more tables in the model, there will only be interactivity with the StartDates, because the relationship of EndDate, is inactive. Or am I doing something wrong?

[Anonymous]

Hi @franciscogsp ,

These two will affect the interaction.

Edit interactions.

Cross filter direction. In this case, Table 1 can interact with Table 2, while Table 2 cannot interact with Table 1.

You may also consider providing a sanitized sample file and we will test it further.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data