Displaying multiple dates across a single x axis

[kenyonca]

I am trying to display different stages of our planning on the same line and bar graph.

To simplify my data that I have pulling from SmartSheet:

Work Plan ID     Draft Plan Date     Design Need Date    IFC Plan Start Date      Actual Construction Start

TB-GR-37           3/6/2017                          7/9/2017                8/8/2017                         9/7/2017

TB-CU-44           3/24/2017                       7/13/2017               8/12/2017                       9/11/2017

TB-GR-15           3/3/2017                         7/14/2017              8/10/2017                        9/7/2017

there are approximately 750+ lines with this type of data

With this I am hoping to show based on the month (also drill down to show the week) how many plans we need to have that month: Draft Plan Date, Design Need Date, IFC Plan Start Date, and Actual Construction Start.

I have tried using a Date Table and have that displaying on the x-axis. I then need the count of the different dates as the y-axis. However, when I try to do that I always get the counts as being the same for each of the 4 date categories. I believe this has to do with only being able to use the relationship only once between the Date Table and one of the 4 date categories.

Is there any way to display multiple dates for a single line across the same line and bar chart?

Thanks

[danrmcallister]

There might be a more-slick option for this, but my thought would be to do the following:

• Add 4 columns to your Date table - one for each date you want to track.  The columns will be the count of each type of date you want to ID
• The formula for each column will be something like:

ActualConstructionStartDateCount = 
CALCULATE(Countrows(WorkDate),
    Filter(WorkDate,WorkDate[ActualConstructionStart] = 'Date'[Date]) 
    )

Where WorkDate is the table you share a screenshot of above.  Result is something like this with the 3 records you shared:

Thoughts?

Dan

[kenyonca]

Thanks for the suggestion @danrmcallister

I'm fairly new to Power BI

I'm trying out this solution but it wont seem to work. For reference my dates for the 4 different date sets are under the "CWP Status".

My Date Table just includes all dates from project start to finish.

Please see below shot

[danrmcallister]

Sure @kenyonca, so what I am suggesting is that you add a few columns to your date table - one each for each type of date you want counted.  In your date table with all dates then you'll have a summary of how many projects have an "ActualConstructionStartDate" (etc) on that specific day.  Please correct me if that's not what you are looking for!

Dan

[kenyonca]

Thats a good workaround for that issue thanks @danrmcallister. I should maybe have mentioned there is still quite a few different filters that we are trying to run such as Discipline, Location, Work Area, Actual Labor MHRs, etc. This data is all on the same rows as those other dates.

With that when I try to apply those filters they dont make any changes to the data. My guess is this because the "count" function used doesnt take into account any of this information.

[kenyonca]

That works perfectly!!!

Thanks for all the help @danrmcallister

Cameron

[danrmcallister · Accepted solution]

@kenyonca You're correct, I don't think you would be able to use those filters.  How about this as another option?  What we need to do is unpivot your dates in the Query Editor.  I added Location as a column in my sample dataset to use as an example.

In the Query Editor I selected my date columns and clicked Unpivot Columns

The result is this; you can rename "Attribute" and "Value" if you want, but now you have a list of dates that are being described by the attribute.

You can now set up a relationship between your Date table and your single Date column in your table

I can build a bar chart that looks like this:

And filter on location using a Slicer.

Does this meet all your requirements?

[kenyonca]

I am trying to display different stages of our planning on the same line and bar graph.

To simplify my data that I have pulling from SmartSheet:

Work Plan ID     Draft Plan Date     Design Need Date    IFC Plan Start Date      Actual Construction Start

TB-GR-37           3/6/2017                          7/9/2017                8/8/2017                         9/7/2017

TB-CU-44           3/24/2017                       7/13/2017               8/12/2017                       9/11/2017

TB-GR-15           3/3/2017                         7/14/2017              8/10/2017                        9/7/2017

there are approximately 750+ lines with this type of data

With this I am hoping to show based on the month (also drill down to show the week) how many plans we are scheduled for. I need the other columns to display the other count of dates; Design Need Date, IFC Plan Start Date, and Actual Construction Start.

I have tried using a Date Table and have that displaying on the x-axis. I then need the count of the different dates as the y-axis. However, when I try to do that I always get the counts as being the same for each of the 4 date categories. I believe this has to do with only being able to use the relationship only once between the Date Table and one of the 4 date categories.

Is there any way to display multiple dates for a single line across the same line and bar chart?

Thanks