Forum Discussion

reast's avatar
reast
Icon for Helper II rankHelper II
10 years ago

Filter Active Clients by Date Range

I'm trying to figure out how to use the time slicer to filter active clients. They don't have a single date field to link with the calendar. They have a start date and an end date (or it's blank if t...
Need Help
MattAllington's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
10 years ago

How about this.

 

Fill you blanks in the End Date column with something like 31/12/9999

Create 2 new tables that consist of a single column of the dates you want to filter on, Start[Date] and End[Date]

 

Put a slicer on the Start[Date]

Put a slicer on the End[Date]

Write these helper measures

 

Selected Start = lastdate(Start[Date])

Selected End = lastdate(End[Date])

 

Write a measure that filters the customers.  Something like this.

 

=Calculate(countrows(customers),filter(customers, customers[Start Date] >=[ Selected Start] && customers[End Date] <= [selected End]))

reast's avatar
reast
Icon for Helper II rankHelper II
10 years ago

Ok. Thank you for clarifying. Now I'm getting this error.

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    10 years ago

    The first parameter of your filter function is a column - that is not allowed. You will note in my formula it is a table. 

    • reast's avatar
      reast
      Icon for Helper II rankHelper II
      10 years ago

      Thanks for your patience. I fixed the measure: Measure = Calculate(countrows('Clients'),filter('Clients', 'Clients'[INTAKE_DATE] >=[Selected Start] && 'Clients'[Discharge] <= [selected End])).

      However, it still only seems to be pulling new clients in the date range, not all active clients (should be over 300).