How to create date range slicer from start/end dates of table instances?

Question

I have two tables - the first contains path IDs, each with a start date, and an end date. The second contains work item IDs, each with a created date, and a closed date.

I would like a way to select a path ID and have it display two tables, one with all work items created during that range and one with work items with closed dates within that time range.

I am currently doing this with two separate slicers, one for created date and one for closed date, and manually entering the time ranges for the paths that I want to look at, but I do not believe this is the optimal solution.

Example tables below:

Paths

    Path                Start Date           End Date

    P1/1                 1/1/2000            4/3/2000

    P1/2                 4/4/2000            8/7/2000                          

    P2/1                 8/8/2000           12/12/2000

Work Items

 Work Item ID          Created Date        Closed Date

      1                  1/2/2000           3/20/2000

      2                  4/5/2000           8/4/2000

      3                  3/2/2000           9/22/2000

      4                  7/5/2000          11/14/2000

      5                  1/2/2000           3/2/2000

      6                  9/5/2000          10/24/2000

Desired output when selecting P1/2:

Created during P1/2

  Work Item ID        Created Date

        2               4/5/2000

        4               7/5/2000

Closed during P1/2

  Work Item ID        Date Closed

       2               8/4/2000

Any help would be very greatly appreciated!!!

Alexis Olson Alexis Olson · Accepted Answer · 2019-07-18T20:54:48

I think you can just look at the min of the start dates and max of the end dates for the paths you've selected.

Something along these lines should work even if you have multiple paths selected:

Created Date =
VAR WorkItemCreated = MAX ( 'Work Items'[Created Date] )
RETURN 
    IF ( WorkItemCreated >= MIN ( Paths[Start Date] ) &&
         WorkItemCreated <= MAX ( Paths[End Date] ),
         WorkItemCreated )

If the condition doesn't hold, it returns a blank and that row should be suppressed.

How to create date range slicer from start/end dates of table instances?

1 Answers