Forum Discussion

tagban's avatar
tagban
Helper I
2 years ago
Solved

People active during Date Range

I've gone thru a few dozen posts that seemingly asked t he same question but the answers never quite worked for me so I'm going to post this in hopes it helps me and others. We have members that join...
Need Help
  • tagban's avatar
    tagban
    2 years ago
    Number of Active People = 
VAR _selDateMin = MIN('Date'[Date])
VAR _selDateMax = MAX('Date'[Date])
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[id] ),
        FILTER (
            'Table',
            'Table'[enrollment_start] <= _selDateMax
                && (
                    'Table'[enrollment_end] >= _selDateMin
                        || ISBLANK ( 'Table'[enrollment_end] )
                )
        )
    )

    This was the fix for this. It allows me to filter anyone that was active at some time during this time. This was largely thanks to you but this was the actual solution. I just needed to account for the MIN and MAX. 

    Thank you for all of your help Anonymous 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi tagban ,

Please update the formula of measure as below and check if can return the expected result...

Number of Active People = 
VAR _seldate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[id] ),
        FILTER (
            'Table',
            'Table'[enrollment_start] <= _seldate
                && (
                    'Table'[enrollment_end] >= _seldate
                        || ISBLANK ( 'Table'[enrollment_end] )
                )
        )
    )

Best Regards

tagban's avatar
tagban
Helper I
2 years ago

Tried this out and it seems to be better, but still having some issues. I -think- I know what the problem is, I think the measure isn't accounting for the min side of date table. IE when you have a slider you're really getting a range of dates like a list right? So you'd look for enrollment start <= minimum(date), and enrollment end is >= max(date)

But the ttruth is, I actually don't care if they leave during that time, what we're looking for is the total people active during those two thresholds.

So for instance someone joins on 10/30/2023 and leaves on 2/25/2024. The slider would be set to 10/20/2023 - 11/2/2023

That person would be considered enrolled during that time. Despite starting after the start time, and ending after the end time.

The same would hold true with the same filter for someone that enrolled on 10/29/2023 and left on 11/1/2023, Still active at some point in that window.

 

Just testing the formula you provided unfortunately shows still the inaccuracy of the min date, but it seems to be closer to what I'd expect at the max (Single point in time).

Like anyone in the above window should also be in this next one:

 

  • tagban's avatar
    tagban
    Helper I
    2 years ago
    Number of Active People = 
VAR _selDateMin = MIN('Date'[Date])
VAR _selDateMax = MAX('Date'[Date])
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[id] ),
        FILTER (
            'Table',
            'Table'[enrollment_start] <= _selDateMin || 'Table'[enrollment_start] < _selDateMax
                && (
                    'Table'[enrollment_end] >= _selDateMax || 'Table'[enrollment_end] >= _selDateMin
                        || ISBLANK ( 'Table'[enrollment_end] )
                )
        )
    )

    I tried tweaking it a bit, but it seems to still ignore the minimum date, but I feel like this might make more sense to what I'm trying to do?

    • tagban's avatar
      tagban
      Helper I
      2 years ago
      Number of Active People = 
VAR _selDateMin = MIN('Date'[Date])
VAR _selDateMax = MAX('Date'[Date])
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[id] ),
        FILTER (
            'Table',
            'Table'[enrollment_start] <= _selDateMax
                && (
                    'Table'[enrollment_end] >= _selDateMin
                        || ISBLANK ( 'Table'[enrollment_end] )
                )
        )
    )

      This was the fix for this. It allows me to filter anyone that was active at some time during this time. This was largely thanks to you but this was the actual solution. I just needed to account for the MIN and MAX. 

      Thank you for all of your help Anonymous 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi tagban ,

        It's glad to hear that your problem has been resolved. Thanks for sharing your solution here. Could you please mark your post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours.  Thank you.

        Best Regards