Forum Discussion

kenyonca's avatar
kenyonca
Advocate I
9 years ago
Solved

Displaying multiple dates across a single x axis

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

Need Help
  • danrmcallister's avatar
    danrmcallister
    9 years ago

    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?

7 Replies

  • danrmcallister's avatar
    danrmcallister
    Resolver II
    9 years ago

    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's avatar
      kenyonca
      Advocate I
      9 years ago

      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's avatar
        danrmcallister
        Resolver II
        9 years ago

        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's avatar
    kenyonca
    Advocate I
    9 years ago

    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