How to turn stacked Bar chart to GANTT chart with duration in minutes (Power BI)

   Check list of all posts

This document is intended to explain how to turn the stacked bar chart into the GANTT chart or turn the first chart into the last chart in this article.

We can create first chart with

Axis = 'Timing'[Start Date Time] & " [" & 'Timing'[Job] & "]"

Duration = DATEDIFF('Timing'[Start Date Time],'Timing'[End Date Time],MINUTE)

 

Then we can figure out the overall start date-time as

Overall Start Datetime =

CALCULATE (

MIN ( 'Timing'[Start Date Time] ),

REMOVEFILTERS (),

VALUES ( 'Timing'[Business Date] )

)

Then get the start Point for each bar

Start Point =

VAR OverallStartDatetime = [Overall Start Datetime]

VAR StartDatetime =

CALCULATE ( MIN ( 'Timing'[Start Date Time] ) )

VAR Result =

DATEDIFF ( OverallStartDatetime, StartDatetime, MINUTE )

RETURN

Result

Add it as first measure

We want show each bar as different color as specified, however, we can't use conditional format any more with current version. the only solution is to create one measure for each Job, and then stack them together

Duration (Job1) =

IF (

MAX ( 'Timing'[Job] ) = "Job1",

MAX ( 'Timing'[Duration] )

)

Duration (Job2) =

IF (

MAX ( 'Timing'[Job] ) = "Job2",

MAX ( 'Timing'[Duration] )

)

Then assign color to each measure.

Mark the start point measure as White

****1 We can also combine chart with status to accommodate job status by adding more colour as below

Duration (Job1) =

IF (

MAX ( 'Timing'[Job1] ) = "Job1"

&& MAX ( 'Timing'[Status] ) <> "In Progress"

MAX ( 'Timing'[Duration )

)

Duration (In Progress) =

IF (

MAX ( 'Timing'[Status] ) = "In Progress",

MAX ( 'Timing'[Duration ] )

)

****2 We can also add constant line  to show such as EOD

EOD =

VAR OverallStartDatetime = [Overall Start Datetime]

VAR EODTime =

DATE ( YEAR ( OverallStartDatetime ), MONTH ( OverallStartDatetime ), DAY ( OverallStartDatetime ) )

+ TIME ( 23, 59, 0 )

VAR result =

DATEDIFF ( OverallStartDatetime, EODTime, MINUTE )

RETURN

result

****3 We can also make chart with hierarchy from event level to drill up and down. In this case, we need to change Duration and Status as measures:

Event Status =

VAR NumberOfJobs =

COUNTROWS ( 'Timing' )

RETURN

IF ( NumberOfJobs > 1, "Composit Jobs", MAX ( 'Timing'[Status] ) )

Event Elapsed Duration=

VAR StartDatetime =

CALCULATE ( MIN ( 'Timing'[Start Date Time] ) )

VAR EndDatetime =

CALCULATE ( MAX ( 'Timing'[End Date Time] ) )

VAR Result =

DATEDIFF ( StartDatetime, EndDatetime, MINUTE )

RETURN

Result