Date slicer in Gantt charts

[Razaning]

Hey folks! 

I'm currently building a project management dashboard using Gantt chart (by Microsoft Corporation) but there is this one requirement that I couldn't figure out how to implement. 

First of all, for each project I have multiple phases and for each phase I have start and end date.

The requirement is when the user filter by year, it should only show phases in that year.. regardless when this phase started and ending. 

Ex. If the project start 2020 and end 2024 - and the user filter 2023... it should only show 2023 phases, if a phase-2 start Nov 2023 and end Feb 2024.. they don't want 2024 to be in the view (I hope I explained it well) 

I've added a custom column to calculate/list all dates in between start and end date: {number.from[start_date]..number.from[end_date]} 

And used it as a filter hoping that it will help presenting only a certain year, but this didn't solve my issue. *And this is quite confusing to end user why when they filter a year it will show more than the selected period*.

Please see attached photo, its same as what I'm trying to achieve: 

 

Does anyone have any idea how this can be done?

[v-yueyunzh-msft · Accepted solution]

Hi , @Razaning 

According to your description, you want to filter the date in the Gantt visual by year

Here are the steps you can refer to :
(1)This is my test data:

(2)We can create a Date table as StartDate in Gantt and we do not need to make any relationship between tables.

Date = CALENDAR(FIRSTDATE('Table'[StartDate]), LASTDATE('Table'[EndDate]))

(3)Then we can create a measure like this:

Measure = var _start_date = MAX('Date'[Date])
var _cur_task_start =MAX('Table'[StartDate])
var _cur_task_end = MAX('Table'[EndDate])
var _days =VALUE(FORMAT( _cur_task_end - _cur_task_start , "0"))
var _cur_year =DATE(YEAR( MAX('Date'[Date])),12,31)
var _cur_year_1 = DATE(YEAR( MAX('Date'[Date])),1,1)
var _days2 = IF(_cur_task_end> _cur_year ,VALUE(FORMAT( _cur_year-_cur_task_start, "0")),_days)
var _end2 = IF(_cur_year<_cur_task_end,_cur_year,_cur_task_end)
var _days3 = VALUE(FORMAT( _end2-_cur_year_1, "0"))
return
IF( _cur_task_start <_cur_year_1 && _cur_task_end >=_cur_year_1 && _start_date=_cur_year_1 , _days3,IF(_start_date=_cur_task_start,_days2,BLANK()))

(4)Then we can put the fields on the visual and the result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[drgrd13]

Hi Aniya,

Your approach seems to work with the timeline to filter years, But can we do the same to filter the weekends from the timeline and also filter non-working . Please can you help https://drive.google.com/file/d/1UuNaeMzNxB7kR02o6FOAbhc8-yiloTmD/view?usp=sharing  hours?

[Razaning]

Hi, @v-yueyunzh-msft 

Thank you for your reply. Yes, this is exactly what I'm looking for!! 

However, i tried to replicate your dashboard and its working wonderful but when I select a date from the date filter, a get an error message. 

Please see attached photo 

 

[v-yueyunzh-msft]

Hi, @Razaning 

I test it in my side , it did not show any error code and i am not surely understand " when I select a date from the date filter".

And for this error code , you can try yo check the [StartDate] and [EndDate]'s type in your table if they are Date format.

If this can not help you solve it , can you try to give me a sample .pbix to test(without sensitive data in it), you can put it to OneDrive and share the link to us?

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Razaning]

Hi - @v-yueyunzh-msft,

Thank you for your support. That error was due to some null in the date column.