Forum Discussion

shaunguyver's avatar
shaunguyver
Helper III
8 years ago
Solved

Clustered Column Chart with Continuous Time X-Axis

I have a SharePoint list with the following data:

 

  1. Job Name
  2. Job Type (two options)
  3. Start Date
  4. End Date

I need to create a clustered bar chart with the X-Axis being the timeline on which the jobs fall (eg. if there are 10 jobs where the start date is prior to January and the end date is after January, then January should show '10').

 

What is the best way to do this? I have created one, but am only able to display when items start. Also, my X-Axis skips entire months if there are no values in them.

 

Thanks!

Need Help
Show and Tell
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    Months table -> Enter Data

     

    Jobs table, that's what I called my mock table of the table specification that you outlined, Job Name, Job Type, Start Date, End Date.

     

    Here is the Jobs table that I used:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s9LVdJRCqksSHUE0ob6hvpGBoYWEKYBhB2rE60UUp6PoswIocwESVVGUSqqccZIxpmC2OZghW75pUVI6owR1hpD1UEMdMssS0VRZ4KkzgyqLhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Job Name" = _t, #"Job Type" = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Name", type text}, {"Job Type", type text}, {"Start Date", type date}, {"End Date", type date}})
in
    #"Changed Type"

    and here is the Months table that I used:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlSK1YlWcktNKoJzfBOLkjPALMeCoswcqBhQLhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}})
in
    #"Changed Type"

     

     

     

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    One method might be to create a table called Months with data like:

     

    January

    February

    March

    April

    May

    ...

     

    In your jobs table create a column called Month with the formula:

     

    Month = IF(MONTH([End Date])=MONTH([Start Date]),FORMAT([Start Date],"mmmm"),"Other")

    Not sure what you want to do with jobs that span months.

     

    Relate the two tables Months 1->*Jobs

     

    Create this measure:

     

    JobCount = COUNTROWS(Jobs)

    Put Months[Month] in your Axis and JobCount as your Value. Click the drop down on Month in Axis and choose "Show items with no data".

     

    If this does not fully address your scenario. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    • shaunguyver's avatar
      shaunguyver
      Helper III
      8 years ago

      Thanks for your reply. Couple of questions (please) before I attempt a fix:

       

      • When you say 'create a months table', do you mean Modelling > Calculations > New Table?
      • By 'jobs table', do you just mean insert a measure into my current data set?

       

      Thanks!

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        8 years ago

        Months table -> Enter Data

         

        Jobs table, that's what I called my mock table of the table specification that you outlined, Job Name, Job Type, Start Date, End Date.

         

        Here is the Jobs table that I used:

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s9LVdJRCqksSHUE0ob6hvpGBoYWEKYBhB2rE60UUp6PoswIocwESVVGUSqqccZIxpmC2OZghW75pUVI6owR1hpD1UEMdMssS0VRZ4KkzgyqLhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Job Name" = _t, #"Job Type" = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Name", type text}, {"Job Type", type text}, {"Start Date", type date}, {"End Date", type date}})
in
    #"Changed Type"

        and here is the Months table that I used:

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlSK1YlWcktNKoJzfBOLkjPALMeCoswcqBhQLhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}})
in
    #"Changed Type"