Gantt Chart: Limit time interval shown (or zoom on specific interval)

[JeanMartinL]

Hello everyone,

I'm currently building a report with a Gantt chart and there is one requirement that I've not managed to figure out how to implement. I've tried multiple Gantt visuals (Microsoft, MAQ, Acterys, xViz, Adwise) and I am not attached to any so I would use whatever works (assuming I can stay with the free version for those with a paid option).

My requirement is that I need to be able to Focus on a given time interval based on another visual. For example let's say I have a Year/Month slicer, if I select March and April 2021, the Gantt chart should "zoom" in and only display these months.

I can filter the data itself easilly and display only the tasks that are part of the selected months but I can't figure out how to filter the Min/Max of the Gantt chart itself. Let's say I have a task that spans the whole year 2021, it means the Gantt chart will always show the whole 2021 year.

I can see 2 types of "solutions". Either I find a way to control the Min/Max of the Gantt visual (no success there) or I trick the visual by dynamically modifying the Start/End date of my tasks to fit the time selection (e.g. my 2021 task becomes a 1st March to 30th April task while the filter is active)

My first idea was creating 2 measures that would basically truncate the start and end dates to the dates selected by the slicer, something like:

StartDateTruncated = 
VAR StartDate = MIN('Projects'[StartDate])
RETURN IF(ISBLANK(StartDate), BLANK(), MAX(MIN('Date'[Date]), StartDate))

But unfortunately most Gantt visuals do not accept measures in the Start/End date parameters. MAQ does accept a measure but for some reason I can only get the Start date to be truncated, the End Date always go up to the current date.

Since the time filters will be dynamic (slicers, other visuals, etc.) I can't use a calculated column.

That's where I am at, does anyone have any ideas?

[v-deddai1-msft · Accepted solution]

Hi @JeanMartinL ,

I don't find any option or workaround to zoom the gantt chart in a single month by slicer. I suggest you create an idea for it in https://ideas.powerbi.com/forums/265200-power-bi-ideas to improve the power bi.

You can only to show events in the progress. Here is a good sample for your reference: https://darren.gosbell.com/2020/02/power-bi-how-to-make-the-gantt-chart-show-events-in-progress/

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[JeanMartinL]

For the record, I have posted the idea here: https://ideas.powerbi.com/ideas/idea/?ideaid=72806ec5-529c-eb11-89ee-281878bdee4b

[JeanMartinL]

What I find very interesting in your link is that he managed to place a measure in the "End Date", if I could manage to do the same I might be able to do my "DateTruncated" trick. I will try to figure out why his measure works and mine doesn't.

Thanks!

[JeanMartinL]

Unfortunately we can apparently only add a measure in the "End Date", that always worked for me as well it seems. I didn't manage to add a measure in "Start Date" both in my own code and in the sample file provided by the code above.

This is unfortunate, I'm so close to a solution.

[HarishKM]

@JeanMartinL Hey You can use this one . A custom viz that let you put start and end date .

ENd result will be 

[JeanMartinL]

Hello @HarishKM,

Thank you for your response.

As you can see in my screenshot, I have already tested this visual (as well as many others). My issue is not displaying the data (there are 5-6 Gantt visual in the store) or even filtering the data (I can create a calculated table extrapolating the dates between Start/End and link those to a Date Table).

My issue is filtering the Visual itself. For example in your own example, by keeping the same data set, would you be able to display only the Month of September (while keeping the September task that starts in September and end way later in November+)?

To reformulate, I want to be able to display only a portion of a task. Even if the task is 1 year long, I want to be able to zoom in a single month.

Thanks!

[v-deddai1-msft]

Hi @JeanMartinL ,

It's impossible to filter across multiple date fields in a date slicer. You can try to set two date slicer one for start date and the other one for end date. 

You can also try to add a calculated table to expand detail records between two date field and link to original table,please refer to  https://community.powerbi.com/t5/Desktop/Spread-revenue-across-period-based-on-start-and-end-date-sl...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[JeanMartinL]

Hello @v-deddai1-msft,

Thank you for your response.

To clarify, I'm already using a calculated table to filter the dates. As mentioned above, I have no issue filtering the data, if I slice on "March" I see only tasks that are in March.

My issue is filtering the Gantt visual itself, while I see only tasks that are March, these tasks themselve can cover many months (e.g. January to April) so the visual itself displays January to April, while I trying to get it to "zoom" or "focus" on only March.

See the screenshot above, the idea here is that I selected March and April, the data is filtered fine (there are many other rows in the dataset) but I would like the visual to only display March and April and not the other months.

Thanks!