topic Re: Distinct count of a column based on start and end dates in DAX Commands and Tips

Distinct count of a column based on start and end dates

elinevans — Fri, 02 Jul 2021 14:51:46 GMT

Here is the data I'm using. Each client can have more than 1 service and I want to find clients active in a service within a time period (say between date x and date y) defined by a slicer on the report. The slicer is based off a date table created by using CALENDARAUTO(). So essentially I want to find how many clients have a start date before y AND end date after x. Where x and y can be altered by a date slicer.

I orignally tested it without a slicer:

No clients in date date = CALCULATE(DISTINCTCOUNT(Table1[Client ref]), FILTER(Table1, Table1[Start Date]<=date(2020,7,4) && Table1[End Date]>=date(2020,5,1)))

for dates between 1st May 2020 and 4th July 2020 and got the correct answer, which is 3. The clients in this case would be 1,4,5.

But when I tested it using the max/min dates of the slicer:

No clients in date wslice = CALCULATE(DISTINCTCOUNT(Table1[Client ref]), FILTER(Table1, (Table1[Start Date]<=max(Datetable[Date]) && Table1[End Date]>=min(Datetable[Date]))))

I get 5. Which is incorrect.

I've tried it with different relationships between start/end date and date table but I just can't get it to work.

Any help, or explaination as to why this isn't possible would be greatly appreciated!!

Thanks

Re: Distinct count of a column based on start and end dates

Jihwan_Kim — Sat, 03 Jul 2021 03:15:42 GMT

https://www.dropbox.com/s/pmukzses4xazvig/elinevans.pbix?dl=0

Re: Distinct count of a column based on start and end dates

elinevans — Wed, 07 Jul 2021 09:51:47 GMT

Thank you!!

Re: Distinct count of a column based on start and end dates

elinevans — Wed, 07 Jul 2021 10:54:41 GMT

If I then added another table

Client ref
1	Female
2	Male
3	Nonbinary
4	Female

and created a relationship between the client ref on the client table and service table and wanted to create a pi chart of the different genders, is there a formula to only include the genders of those who are active?

I'm currently doing

genderfemale = CALCULATE(count(Client[Gender]),filter(Client, Client[Gender]="Female"), FILTER(ServiceInfo, ServiceInfo[StartDate] <= MAX(DateTable[Date]) && ServiceInfo[EndDate] >= MIN(DateTable[Date])))

to get the active females which seems to work but it means I have to do it for every gender (and some other classifications).

Is there a way to do it in a more efficient way?