Forum Discussion
Helper IVisualizing Data values between dates
Hello Friends,
I am trying to build out a visulization that will show me how many reports (Reports Goal) between the time periods below.
So one example would be between 1/2-1/15 there would be 172 reports that were due. What I would like to do is have a line chart that shows the ebs and flows of what the goals are over time.
So almost like a Gantt Chart but not ha!
| Person | Start | End | Reports Completed | Reports Goal |
| Macgyver | 1/1/2022 | 1/15/2022 | 110 | 100 |
| Johnson | 1/2/2022 | 1/15/2022 | 62 | 80 |
| Johnson | 1/4/2022 | 2/1/2022 | 65 | 65 |
| Grace | 1/15/2022 | 2/4/2022 | 80 | 110 |
| Sosa | 5/3/2022 | 5/30/2022 | 100 | 125 |
| Sosa | 4/6/2022 | 5/1/2022 | 100 | 100 |
| Grace | 4/1/2022 | 5/18/2022 | 65 | 65 |
| Macgyver | 3/4/2022 | 3/15/2022 | 12 | 23 |
find the pbix attached
10 Replies
- lbendlin
Super User
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdDNCsMgDAfwd8m5YIwf+AaDwU47igcpZTtVaGGwt5/a2kjXS4j4U//Re3jE8fX9TAsMIIUUhERba45eYqmIEAYP9/Se1zRXQ1fcluL+sW6A+BlrtlLsbYnjdLqL+JTDPUqxz7TGvDRCte3c4pEGqyXTWy0sW3mi+2wtgmaQrbtK232b4pCq/7Y6gIIQfg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Start = _t, End = _t, #"Reports Completed" = _t, #"Reports Goal" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Person", "Reports Completed", "Reports Goal", "Index"}, "Attribute", "Value"), #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Reports Completed", Int64.Type}, {"Reports Goal", Int64.Type}}) in #"Changed Type1"You have multiple intervals per person, so I had to add an index column.
spider_Monkeylbendlin Alright so I added an indexed column, but this might be a dumb question...This is off a table that I build so I can't see it in the query editor.
- lbendlin
Not sure what you mean. Unless you connect to a dataset or dataflows you should be able to see the query in the query editor.
- spider_Monkey
lbendlin Can you send a screen shot of the table view to see how it looks after the edit to the query editor?
- lbendlin
find the pbix attached
- spider_Monkey
Thanks lbendlin I'll play with adding this in, I appreciate your help!
- spider_Monkey
So I built a new table outside of query editor based off data from anohter table.
- lbendlin
As long as you included the index column there you are good. Doesn't matter where in the ETL process you do it.
- spider_Monkey
Okay, so I am using union to unpivot this and I should be able to get there. Do you happen to have a screen shot of the visualizations pane from the graph you provided?