Forum Discussion

yuvi12's avatar
yuvi12
Frequent Visitor
2 years ago

Help for Date range slicer only affect the data bars not data.

when users select a date range using the slicer, the Gantt chart will dynamically adjust to display only the project activities that fall within the selected date range while still showing all projec...
Sahir_Maharaj's avatar
Sahir_Maharaj
Icon for Super User rankSuper User
2 years ago

Hello yuvi12,

 

Here is a basic guide that might assist:

 

1. Ensure your data has columns for ProjectName, ActivityStartDate, and ActivityEndDate. Create a a slicer visual onto your canvas and use the ActivityStartDate as the field.

2. Use a Gantt chart custom visual from the Power BI marketplace if you haven't already done so

3. This is the trickier part. You want the Gantt chart to show all projects but only highlight the activities within the selected date range:

Filtered Start Date = 
IF(
    MIN('Table'[ActivityStartDate]) >= MIN(SlicerTable[ActivityStartDate]) && 
    MAX('Table'[ActivityEndDate]) <= MAX(SlicerTable[ActivityEndDate]),
    MIN('Table'[ActivityStartDate]),
    BLANK()
)

Filtered End Date = 
IF(
    MIN('Table'[ActivityStartDate]) >= MIN(SlicerTable[ActivityStartDate]) && 
    MAX('Table'[ActivityEndDate]) <= MAX(SlicerTable[ActivityEndDate]),
    MAX('Table'[ActivityEndDate]),
    BLANK()
)

Should you require any further assistance, please do not hesitate to reach out to me.

yuvi12's avatar
yuvi12
Frequent Visitor
2 years ago

This approach didn't work for me because I only have one date dimension field to slice the data. How can I obtain the start and end dates for the slicer in the report? Currently, the report shows nothing, and there is no relationship between the slicer date field and the activity date fields. In the past, I used the date range concept, but the use case is different now.