Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a SharePoint list with the following data:
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!
Solved! Go to 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"
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
Thanks for your reply. Couple of questions (please) before I attempt a fix:
Thanks!
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |