Filter by Key Date - data with start and end dates

[PauloH]

Hi,

I have a database with multiple tables that contain start and end date for employee details, I want to allow users to select a key date and display the data that the date falls into. What's the best way to go about this?

Say we have:

Employees

-- Employee Unique ID

-- Name

Position

-- Employee Unique ID

-- Position Title

-- Employment Status

-- Start Date

-- End Date

Performance Score

-- Employee Unique ID

-- Score

-- Start Date

-- End Date

Thanks!

[CahabaData · Accepted solution]

in general the method for user selection is via the Slicer;  some good video tutorials on using these.  You can have multiple within a page of a report.  So I would first check out that information.  

I can understand the idea of "slicing & dicing" data sets - but have always thought a more accurate name would be a Filter Selector......

make your first stab with these and then post if you need further assist.

[Eric_Zhang · Accepted solution]

---

@PauloH wrote:

Hi,

I have a database with multiple tables that contain start and end date for employee details, I want to allow users to select a key date and display the data that the date falls into. What's the best way to go about this?

Say we have:

Employees

-- Employee Unique ID

-- Name

Position

-- Employee Unique ID

-- Position Title

-- Employment Status

-- Start Date

-- End Date

Performance Score

-- Employee Unique ID

-- Score

-- Start Date

-- End Date

Thanks!

---

@PauloH

You can create a independent calendar table used in the slicer and create a measure as

isShown = 
IF (
    ISFILTERED ( 'calendar'[Date] ),
    IF (
        MAX ( 'calendar'[Date] ) >= MAX ( Table1[start date] )
            && MAX ( 'calendar'[Date] ) <= MAX ( Table1[end date] ),
        1,
        BLANK ()
    ),
    1
)

To filter data, you can use a filter like

FILTER(yourTable, yourTable[start date]<= MAX(calendar[date])&&yourTable[end date]>= MAX(calendar[date]))

See the attached pbix file.

[CahabaData · Accepted solution]

in general the method for user selection is via the Slicer;  some good video tutorials on using these.  You can have multiple within a page of a report.  So I would first check out that information.  

I can understand the idea of "slicing & dicing" data sets - but have always thought a more accurate name would be a Filter Selector......

make your first stab with these and then post if you need further assist.

[ara0530]

This had no relevant info to the question - not a solution