Excel Forum

In Care Slicer or Time Line

Jennifer Owens

Active Member

Members

Power Query

Power Pivot

Forum Posts: 5

Member Since:
September 21, 2023

Offline

June 7, 2024 - 4:26 am

Hello everyone,

I have a dataset that tracks clients in residential programs. This dataset contains begin and end dates. As shown below, a unique client can move from one program to another.

Client Program BEG_DT END_DT
1234 1122 02/15/20 05/06/20
1234 5656 05/06/20 10/11/20
1234 2323 10/11/20 04/16/22
1234 5656 04/16/22 08/12/22
1234 8282 08/12/22 03/18/23
1234 5656 03/18/23 04/15/24*

I'd like to create a dashboard that will allow the user to view data on clients In-Care as of a user-selected date. To identify clients In-Care on a given date (e.g., 12/31/23), I would typically define the population using the logic shown below and then produce the desired charts/graphs for that population.

# In-Care as of 12/31/23 = (BEG_DT <= 12/31/23) AND (END_DT>12/31/23)
*NOTE: This would capture the example client 1234 as in-care at Program 5656 as of 12/31/23

I'm not sure how to create a date filter that (1) lets the user enter (or select) the date of interest, and (2) compares the entered/selected date to both the begin and end dates to determine which records/rows should be used for the dashboard. Any thoughts?

It would also be helpful if I could set a minimum and maximum for the possible date entered/selected, so they are only choosing dates out of the range of data available.

Thank you!
Jennifer

See Answer

Mynda Treacy

Admin

Forum Posts: 4772

Member Since:
July 16, 2010

Offline

June 9, 2024 - 10:10 pm

Hi Jennifer,

You can't really do this with a Slicer because you'd have to refresh the PivotTable after changing the date you want to filter for.

In the attached file there's an example using a PivotTable and Slicer and another using the FILTER function, which will give you immediate results.

You can enter the date you want to check for in cell C1. You can use data validation to limit the dates the user can choose from (Data tab > Data Validation > choose 'Date' from the 'Allow' drop down. I've currently set it to any date in 2024 as an example.

I hope that points you in the right direction.

Mynda