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:   Job Name Job Type (two options) Start Date End Date I need to create a clustered bar chart with the X-Axis being the timeline on which the ...
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"

     

     

     

     

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"