How can I apply slicers to Start and End date columns as a date range?

[DRing]

I have gym membership data in a table like below where each record has a customer ID, Membership_Start_Date, and Membership_End_Date, along with many other columns.

My requirement is to provide a table visual with slicers on Membership_Start_Date and Membership_End_Date so that our end users can see a list of people that had a membership on any date on or after the Membership_Start_Date slicer or on or before the Membership_End_Date slicer.

Essentially, I need a way to determine if each record has a date that falls on or between the slicer dates.

I'm thinking I need to create some sort of flag, but I haven't been able to fully wrap my head around how to accomplish this.

[Ashish_Mathur · Accepted solution]

Hi,

I have a similar problem in the attached PBI file.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

I have a similar problem in the attached PBI file.

Hope this helps.

[DRing]

@Ashish_Mathur This was able to get me going in the right direction.

[v-yohua-msft]

Hi, @DRing 

Based on your information, I create a table

Here's my idea of creating a new table with the minimum date being the minimum value of the start date of the membership card and the maximum date being the maximum value of the end date among customers.

Then we tie the date table to the first table and put the dates into the slicer so we can filter the users in the dates. Start with the minimum start date. Create another measure and make a logo for the users who appear, so that you can show the valid users in the range.

My model view:

Following this steps:

1. Create a new table

Date = ADDCOLUMNS(CALENDAR(MIN('Table'[Membership_Start_Date]),MAX('Table'[Membership_End_Date])),"year",YEAR([Date]))

2. Then create a new measure named Logo, There are three more possibilities that we need to consider. The minimum value of the slicer > = the minimum value of the date table &< the minimum value of the date table or the maximum value of the slicer > = the minimum value of the date table &< the maximum value of the date table are within the range of the minimum and maximum values of the slicer.

Logo =

       CALCULATE(COUNTAX(

    FILTER('Table',MIN('Table'[Membership_Start_Date])<=MIN('Date'[Date])&&MIN('Date'[Date])<=MAX('Table'[Membership_End_Date])||

    MAX('Date'[Date])>=MAX('Table'[Membership_Start_Date])&&MAX('Date'[Date])<=MAX('Table'[Membership_End_Date])||(MIN('Table'[Membership_Start_Date])<=MIN('Date'[Date])&&MIN('Date'[Date])<=MAX('Table'[Membership_End_Date]))||(MIN('Table'[Membership_Start_Date])<=MAX('Date'[Date])&&MAX('Date'[Date])<=MAX('Table'[Membership_End_Date]))),('Table'[Customer_ID])),

    CROSSFILTER('Table'[Membership_Start_Date],'Date'[Date],None))

3. The following is my preview

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DRing]

@v-yohua-msft This works on my test data which is a small sample, however it's not working when applied to my real data.

Something is happening when I apply the logo. For example, my table  visual shows a Customer_ID count of 12,181. When I add the Logo field, I get the error below of returning more than 1,000,000 rows.

[v-yohua-msft · Accepted solution]

Hi, @DRing 

This is due to the fact that you have too much data, you can consider converting to Import mode, or optimize your table, try increasing the aggregate function, reducing the number of rows in the query.

Use other ways to optimize it, the following is the optimization link:

Optimization guide for Power BI - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DRing]

@v-yohua-msft Thank you

[amitchandak]

@DRing , Refer Active member, New member and terminating member - HR analytic approach

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

[DRing]

@amitchandak Thank you. Everything in this example is related to producing a count for each month_year. I need a way to produce a flag that identifies if a customer was "active" (had an active membership in my example, was an employee in your example), during the selected period.