Clustered Column Chart with Continuous Time X-Axis

[shaunguyver]

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!

[Greg_Deckler]

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]

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 · Accepted solution]

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"