Calculate Clients in Date Range

DAX DAX Calculations
1

I want to count all Clients that have been seen during a selected date range (via a slicer). The count is to only include Clients in the Individuals Table that also have an ID in the WB-Case Table and were existing or new clients during that time, must only include:

  • Clients that have a Date Entered before or during the selected Date Range AND
  • Clients that have an Exit Date either during or after the selected Date AND
  • Include Clients that have a blank Exit Date (not exited yet)

Individuals (table):

  • Client (column)

WB-Case (table):

  • ID (column)
  • Date Entered (column)
  • Exit Date (column)

DateTable (table):

  • Date (column)
2

Hi @BrittKing - Please share a sample PBIX file to look into this.

Thanks
Ankit J

3

You can try something like this:

Clients in Date Range = 
VAR StartDate = MIN(DateTable[Date])
VAR EndDate = MAX(DateTable[Date])

RETURN
CALCULATE(
    DISTINCTCOUNT(Individuals[Client]),
    FILTER(
        'WB-Case',
        'WB-Case'[Date Entered] <= EndDate &&
        (
            ISBLANK('WB-Case'[Exit Date]) || 
            'WB-Case'[Exit Date] >= StartDate
        )
    )
)

This counts clients who entered on or before the selected date range, and exited during or after it — or haven’t exited yet.
Make sure relationships are set correctly between tables.

1 Like
4

Thank you, it is including those with or without an exit date appropriately however it doesn’t appear to be including those that were entered prior to the selected date range.

1 Like