Adding a dynamic date filter for Gantt Chart

[WanqiYao]

Hi everyone, 

In the most simple terms, I am trying to use a slicer to generate a date filter that interacts with a Gantt chart. What I have done so far is that: I've used a combination of measure and new table to generate selectable timeline and its corresponding measurements for the earliest and latest day of selected timeline. For example, Q4 2024 gives 1/10/2023 for the "StartDate". However I'm stuck getting this to filter the start dates in the other table. 

I've used "StartDate = CALCULATE ( MIN ( date1[Date] ), ALLSELECTED ( date1[Date] ) )" for getting the value and tried to add an additional column in Table View to filter the Gantt chart  by this value. I suspect its because that the selection is done at a different stage, the StartDate measure always only return the earliest day of the Date table instead of the selected value.

Any help and hints is really really greatly appreciated!

Regards,

Peter

[Anonymous]

Hi @WanqiYao ,

I used your measure syntax and it works fine, always returning the minimum date in the filtered period in the Slicer instead of the minimum date in the table.

This is the raw data:

Date
3/1/2024
3/2/2024
3/3/2024
3/4/2024
3/5/2024
3/6/2024
3/7/2024
3/8/2024
3/9/2024
3/10/2024
3/11/2024
3/12/2024
3/13/2024
3/14/2024
3/15/2024
3/16/2024
3/17/2024
3/18/2024
3/19/2024
3/20/2024
3/21/2024
3/22/2024
3/23/2024
3/24/2024
3/25/2024
3/26/2024
3/27/2024
3/28/2024
3/29/2024
3/30/2024
3/31/2024
4/1/2024
4/2/2024
4/3/2024
4/4/2024
4/5/2024
4/6/2024
4/7/2024
4/8/2024
4/9/2024
4/10/2024
4/11/2024
4/12/2024
4/13/2024
4/14/2024
4/15/2024
4/16/2024
4/17/2024
4/18/2024
4/19/2024
4/20/2024
4/21/2024
4/22/2024
4/23/2024
4/24/2024
4/25/2024
4/26/2024
4/27/2024
4/28/2024
4/29/2024
4/30/2024
5/1/2024
5/2/2024
5/3/2024
5/4/2024
5/5/2024
5/6/2024
5/7/2024
5/8/2024
5/9/2024
5/10/2024
5/11/2024
5/12/2024
5/13/2024
5/14/2024
5/15/2024
5/16/2024
5/17/2024
5/18/2024
5/19/2024
5/20/2024
5/21/2024
5/22/2024
5/23/2024
5/24/2024
5/25/2024
5/26/2024
5/27/2024
5/28/2024
5/29/2024
5/30/2024
5/31/2024
6/1/2024

This is the page effect:

You can see he works very well.

I would be grateful if you could provide me with your pbix file, please be careful not to log into your account in Power BI Desktop when uploading the pbix file.

Thank you in advance for your cooperation, it will help us solve the problem in a timely manner.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[WanqiYao]

Hi Yang,
Thank you for the fast response! Reading from your response I think I have failed to mention that I had tried to call the function from a different data source / table to enable use on filtering the information in the data source. This is the additional column (last column) in the Schedule table. The function called only returns limit value rather than the selected value. Please let me know if you have any questions or suggestions.

Please see the link for the google drive account for the PowerBI and the source data:

https://drive.google.com/drive/folders/1eIHU5LjSHiYHF0JSETYpi82xCHD2C2Ld?usp=sharing

Regards,

Wanqi Yao

[Anonymous]

Hi @WanqiYao ,

Calculated columns are columns that are added to an existing table in a Power BI data model. It is calculated during the data loading phase and the value is stored in the Power BI model. The formula for a calculated column is calculated for each row in the table. Will not be affected by Slicer.

Measures are calculations created using DAX (Data Analysis Expressions) that are performed dynamically when interacting with a report in Power BI. Measures are used in visuals, PivotTables, and PivotCharts. Unlike calculated columns, calculations are not precalculated. Will be affected by Slicer.

For more information about the difference between measure and calculated columns, please refer to the following document:

Calculated Columns and Measures in DAX - SQLBI

Difference Between Calculated Column and Measure in Power BI - SPGuides

In the example you gave, just replace the StartDate of the Schedule table with measure with the same syntax.

The visual effect is as follows:

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[WanqiYao]

Hi Yang,

Thank you for clarifying the reason for the error. I understand that this works, however I am undable to understand how to achieve the filter based on the start date and the end date. My original plan was to use an additional column to return 1 if the plan dates include the selected date and 0 vice-versa, then I can use this column to as a filter for the page. As you suggested this doesn't quite work, 

Please let me know if you have any hints or suggestions you can share with me.

Regards,

Wanqi Yao