Date Filtering for Active Records within Range

[Al345]

I have a dataset which tracks multiple stages in a process and I want to count how many people are in a given stage on a certain date. There is one row per status, per person, with a record start and end date. Our users would like to be able to select a given date and see what the count was on that day. Initially I used a before date slicer to filter out records that had a start date after the selected date. The challenge here is that people can be in a stage and then drop their status and re-enter the stage at a later date. With the current model, any date after the first time they enter the status counts them in that stage, even if it's actually in the period that they had dropped that status. 

Is there a way to use a calendar dimension to pull only records where the selected date is between the record start and end date? Or is there a way to pull only the max date so it only pulls the latest status as of the date selected? 

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Al345]

This would be an example of what the dataset would look like: 

ID	Stage 1 Start Date	Stage 1 End Date	Stage 2 Start Date	Stage 2 End Date	Stage 3 Start Date	Stage 3 End Date
123	1/5/2019	1/28/2019	1/29/2019	4/1/2019	4/2/2019	null
456	1/6/2019	2/5/2019	2/6/2019	3/3/2019	3/4/2019	3/8/2019
456	1/6/2019	2/5/2019	2/6/2019	3/3/2019	4/2/2019	null
345	2/1/2019	2/3/2019	2/4/2019	3/1/2019	3/2/2019	3/15/2019

As you can see person 456 has two rows indicating that they have two instances of being in Stage 3. The other status dates for person 456 remain the same. This shows that the person went through Stages 1 and 2 one time and they were in Stage 3 from 3/4 - 3/8 but not between 3/9 - 4/1. They then entered Stage 3 again on 4/2 and remained active in that stage. 

We want to show how many active people there are in each stage at any given time based on a user selected date. So if someone selectes the date 1/8/2019 it should show 2 distinct people in Stage 1 only. If someone selects 2/7/2019 it will show 3 unique people in Stage 1 (we want to count anyone in Stages 1 and 2 they have passed through, even if that is not their most recent stage) and 3 people in Stage 2. If 3/5/2019 is selected it will show 3 people in Stage 1, 3 people in Stage 2, and 2 people in Stage 3 (person 456 and 345). If someone has selected 3/9/2019 only person 345 will be counted as person 456 is not active in Stage 3 again until 4/2/2019. 

We can use a before date slicer for Stages 1 and 2, but that presents an issue with Stage 3 as we don't want to count them unless they are actually active in that stage. 

[Ashish_Mathur]

Hi,

Will there only be 3 stages?  Should a null be interpreted as Today's date?

[Al345]

We have more than three stages. Most are stages like 1 and 2 where we don't have to worry about the end date but there are at least two stages that should be like stage 3 where they only show as active in that stage if it falls between one of the stage 3 start and end dates. 

And yes the null would mean they're still active in that stage as now. 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Al345]

Hi @Ashish_Mathur ,

Thank you! I see two issues with this solution. The first is that we can't tell which stage a person is in, just that they were active in a stage. What we are trying to show is a count per stage, per person, on a given date. The other issue is that when we apply this to our dataset which is fairly large we end up with a dataset of about 500 mil rows. I was thinking Power BI would be able to handle that but I was not able to get the dataset to load when we tried something similar. I'm wondering if there is a solution that would not require us to create a row for each date but could instead use some kind of measure to check if the date selected is between the start and end date. This is common in SQL but I haven't found a good way to replicate it in Power BI. 

[Ashish_Mathur]

Hi,

You are welcome.  I will defeinitely not be able to help you with resolving the second problem.