Measure to filter a gantt chart based on two dates

[angieleman]

I have a table of activities. There is an activity type column, a start date column and an end date column. 

I also have a Dates table, which is summarized to months and has as Month_Year column. There is an inactive relationship between Dates[Month_Year] and the Start and End date columns.

I have a gantt chart visualization that shows each of the activities on a timeline and shades the duration from the start date to the end date. I want to add the Dates[Month_Year] filter to the gantt so the chart filters to show only activities that happen in the month(s) selected.

I have two measures to calculate the first day of the first month selected and the last day of the last month selected.

MinDate = MIN(Dates[FirstofMonth])

MaxDate = MAX(Dates[LastofMonth])

And I have the filter criteria for my CALCULATE measure

FILTER('Combo','Combo'[Activity End Date]>=[MinDate] && 'Combo'[Activity Start Date]<=[MaxDate])

What makes sense to me is a calculated column on the Combo table that uses the comparison formula above. This gets me the row context I need. Then I could add that as a filter field on the chart. But when I tried that, the calculated column didn't account for the months selected in the filter field and everything returned as matching. Is therea way to force a calculated column to recalculate based on a measure?

I also tried a COUNTROWS measure with the filter criteria above, but that just counts the rows that match and doesn't actually filter the chart to only pull the rows counted.

How can I create a measure (or calc column) that will filter the chart to just the rows that meet the criteria? 

[v-kkf-msft · Accepted solution]

Hi @angieleman ,

Please try to delete the relationship between Dates and Combo table. Then create measure.

Measure = 
var MinDate = MIN(Dates[FirstofMonth])
var MaxDate = MAX(Dates[LastofMonth])
var tab = 
    CALCULATETABLE(
        VALUES(Combo[activities]),
        FILTER(
            'Combo',
            'Combo'[Activity End Date] >= MinDate 
            && 'Combo'[Activity Start Date] <= MaxDate
        )
    )
return COUNTROWS( INTERSECT( tab, VALUES(Combo[activities]) ) )

Filter activities column by Measure:

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[sizi]

Hello,

I have similar requirement. I need gantt chart to show previous, current and next month data based Start date and End date. I am trying to show the task each user has based on start and end date. I need visual level filter to define this. Kindly help.

Thanks in advance.

[angieleman]

Thanks Winniz, I'm sure that would work and its nice to have options. I ended up creating this measure:

List of IDActType values =

VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Combo'[IDActType])

RETURN

CALCULATE(

        CONCATENATEX(

            VALUES('Combo'[IDActType]),

            'Combo'[IDActType],

            ", ",

            'Combo'[IDActType],

            ASC

        ),

FILTER('Combo','Combo'[Activity End Date]>=[MinDate] && 'Combo'[Activity Start Date]<=[MaxDate]))

I added this as the filter on my visual and selected "is not blank" and it worked.