cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Razaning
Frequent Visitor

Date slicer in Gantt charts

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: 

JeanMartinL_0-1617820808981.png

Does anyone have any idea how this can be done?

 

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

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:

vyueyunzhmsft_0-1684480089473.png

 

(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:

vyueyunzhmsft_1-1684480115635.png

 

 

 

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

View solution in original post

5 REPLIES 5
v-yueyunzh-msft
Community Support
Community Support

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:

vyueyunzhmsft_0-1684480089473.png

 

(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:

vyueyunzhmsft_1-1684480115635.png

 

 

 

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

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?

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 

image001.png

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

 

 

Hi - @v-yueyunzh-msft,

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors