Stacked Bar chart as a timeline for individual days

[jaymeyer]

Hi

I'm hoping to get help creating a stacked bar graph/timeline similar to the one described in this post: Solved: Stacked Bar Chart as timeline - Microsoft Fabric Community

The result I am looking for should resemble something like this where each activity is a different colour:

Iinitially I tried to create a 'collapsed' gantt chart' but I cannot make each activity a different colour and it's too small:

This is an excerpt of some of the data I am working with for one day. Ideally I would like to show a separate "stacked bar graph timeline" for each day.

[jaymeyer]

Hey! That is almost exactly what I am trying to achieve! Looking at the visual though I'm starting to think this could get a bit complicated.

I've provided a snippet of data for one machine, there are going to be 14 machines in total and I will be wanting to add in a slicer so I can view one machine at a time and view 7 or 8 days at a time (undecided yet). For some machines there will be two different shifts back to back in a 24hr span. How might I deal with this and what might happen when an activity crosses midnight? This will be a very regular occurence.

Thank you very much for your help!

[jaymeyer]

I added another sheet with a couple more machines to provide a better sample of the data. Hopefully that helps:

https://docs.google.com/spreadsheets/d/1lRZOKw-j79jNpJo2SwSWH3foJVSH06-x56IitWaqgPE/edit?usp=sharing

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[jaymeyer]

Apologies for that - here is sample data for 5 days. What I am trying to do is create the 'stacked bar graph timeline' where each day is represented by it's own bar.

I want each day to look like this where the start and end time of an activity is shown by a different colour segment on the bar. Same activity types are shown by the same colour. The length of the bar should always be the same regardless of total minutes worked in a day. Ideally I would want data labels that show the minutes of each segment.

https://docs.google.com/spreadsheets/d/1lRZOKw-j79jNpJo2SwSWH3foJVSH06-x56IitWaqgPE/edit?usp=sharing

[lbendlin]

Before we get into the finer details of the ask  (for example - what should happen when an activity crosses midnight?)

Here is a cheap and cheerful implementation using a Deneb visual.  Is that maybe already good enough?

[jaymeyer · Accepted solution]

Alright, I'm pretty new to this and it took me a while to figure out but I've made some progress and would like your opinion to see if I've done this 'correctly' or if it could be more efficient or if you see any flaws. But this is more or less achieving what I've set out to do and could not have done it without your help!

The main change is I created a "ShiftID" to deal with the wrapping for past midnight shifts as sometimes crews can end and start a shift on the same day:

ShiftID =

[Team]

  & "-" &

FORMAT ( [Date], "ddmmyyyy" )

  & "-" &

[Machine]

This is the updated JSON:

{

  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",

  "data": {"name": "dataset"},

  "transform": [

    {

      "window": [

        {"op": "min", "field": "StartTime", "as": "ShiftStart"}

      ],

      "groupby": ["ShiftID"]

    },

    {

      "calculate": "(datetime(datum.StartTime) - datetime(datum.ShiftStart)) / (1000 * 60 * 60)",

      "as": "OffsetStart"

    },

    {

      // Similarly for EndTime

      "calculate": "(datetime(datum.EndTime) - datetime(datum.ShiftStart)) / (1000 * 60 * 60)",

      "as": "OffsetEnd"

    }

  ],

  "mark": "bar",

  "encoding": {

    "y": {"field": "ShiftID", "type": "nominal"},

    "x": {

      "field": "OffsetStart",

      "type": "quantitative",

      "stack": null

    },

    "x2": {"field": "OffsetEnd"},

    "color": {"field": "Description", "type": "nominal"}

  }

}

[lbendlin]

Looks good. What else do you need?