Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
salim041989
New Member

Date slicer on two table to filter two different dates

Hi team .

 

I have the below two tables

 

Untitled.png

 

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.

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
ryan_mayu
Super User
Super User

@salim041989 

 

Maybe you can try userelationship.

1.PNG

you need to create a calendar table

2.PNG

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]))

3.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.