Graph # of Start Dates against # of Finish Dates per month in clustered column chart

[Anonymous]

Hello All,

Thanks in advance for any help here. 

I have a column of Start dates for projects as well as a column for Finish dates of projects. I would like to have a clustered column chart that shows, by month, how many projects are starting and how many projects are finishing. But I can't seem to get this data organized correctly. 

I've tried creating a DIMDate table, connecting the date field to both Start and Finish columns, with one as active and one as inactive. Then I created a measure to count Start dates using the relationship with DIMDate so I can have MonthName as my X Axis. I did the same for Finish dates, then put both into the chart but can't get the results I'm looking for. 

Is there an easier way to go about this? 

In the attached example Charter = Start and STT = Finish

[Ritaf1983 · Accepted solution]

Hi @Anonymous 

It sounds like your approach was correct. Try checking the steps and seeing where it went wrong.

I'm attaching an example with a scenario identical to what you're describing.

Projects table like :

Relationships with dates table :

Measures :

started = DISTINCTCOUNT('Table'[project id])

eneded_ = CALCULATE(DISTINCTCOUNT('Table'[project id]),USERELATIONSHIP(Calender[Date],'Table'[End date]))

Graph :

Results are accurate :

The pbix is attached

[Anonymous]

@Ritaf1983 Thank you for the help! This looks exactly like what I'm trying to do but even after following your guide step by step I'm struggling. One thing I noticed is that my Date column has a contact card icon next to it and yours doesn't. From looking online this is the explanation for the icon. 

As you can see in my screenshot the Month Names aren't showing and the quanities don't make any sense. 

I've double checked all by data types and they match correctly.  Any thoughts on what I'm doing wrong here?

[Ritaf1983]

Hi @Anonymous 

From the image you attached, I see that there is a problem with the relationships in your model. In other words, you did not correctly link the dates table to the projects table.

The icon is not related, it just says that the date column is a primary key, I did not define my table as a date table for the model so it did not appear, but for example, now I changed it and it appears.

In the specific case of your topic, this has no effect.

To understand the problem with the model relationships, I need to see the pbix. You can save a copy of only relevant data without sensitive data and attach a link.

[Anonymous]

@Ritaf1983 , I had two different ways of connecting to similar data (one imported and one through a dataflow). When I was doing some troubleshooting it looks like I comingled them in the formulas! I've now fixed that and used your approach and things are working as expected 🙂

Thank you for all of the help!

[Ritaf1983]

Happy to help 🙂

 Consider Accepting it as the solution to help the other members find it more quickly.

[Anonymous]

@Ritaf1983 , I just tried publishing the desktop version to service and received the below error. Any thoughts on how I manage the fact that my date table (Calendar) is local and my other data is remote/imported?

[Anonymous]

Please disregard, 
Instead of using the Calendar table in your suggestion I ended up using a different date table that is part of my data import already (DIMDate). This seems to have resolved the issue!