Date slicer on two table to filter two different dates

[salim041989]

Hi team .

I have the below two tables

Both are coming from the same task tables .

I am trying to create a relative date filter where it filters both the tables

For example : If i select the filter in this week on the slicer the table1 should display the tasks where taskstartdate is in this week , and table2 should display tasks where taskfinishdate is in this week .

Any help will be appriciated as i have tried many ways and the filter just doesnot work on both the tables.

[v-shex-msft · Accepted solution]

HI @salim041989,

It seems like a common analytic across multiple date range requirement, I'd like to suggest you create a expand table to expand the records between these date fields and link to raw table. (project id as relationship key)

Expand =
VAR _calendar =
    CALENDAR (
        MIN ( Table[TaskStartDate] ),
        MAX ( MAX ( Table[TaskEndDate] ), TODAY () )
    )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( Table, _calendar ),
            Table[TaskStart] <= [Date]
                && Table[TaskEndDate] >= [Date]
        ),
        "ProjectID", [ProjectID],
        "Date", [Date]
    )

Then you can use new table date fields as slicer to filter records.

Regards,

Xiaoxin Sheng

[ryan_mayu]

@salim041989 

Maybe you can try userelationship.

you need to create a calendar table

startdate is solid line and end date is dot line.

create a enddate measure and drag that to the table visual.

enddate = CALCULATE(max('Table'[enddate]),USERELATIONSHIP(datetime[Date],'Table'[enddate]))