topic Re: Count Distinct Dates Between Start Date and End Date Columns in DAX Commands and Tips

Count Distinct Dates Between Start Date and End Date Columns

bricohen1 — Thu, 09 Jan 2020 17:27:58 GMT

Suppose I have this:

Member	Start	End
member1	1/1/2019	1/5/2019
member1	1/1/2019	1/5/2019
member1	1/1/2019	1/6/2019
member2	3/1/2019	3/2/2019
member2	3/1/2019	3/3/2019

This table reflects six distinct dates for member1: 1/1/19, 1/2/19, 1/3/19, 1/4/19, 1/5/19, and 1/6/19.

..and three distinct dates for member2: 3/1/19, 3/2/19, 3/3/19

Is it possible to create a Measure to compute something like this?

The measure would compute the number of distinct dates per member, based on the Start and End date.

I've been working on this for hours - any ideas would be appreciated.

Thanks!

Brian

Re: Count Distinct Dates Between Start Date and End Date Columns

az38 — Thu, 09 Jan 2020 17:41:21 GMT

Hi bricohen1

what exactly result do you expect?

anyway try a measure

Measure = DATEDIFF(CALCULATE(MIN('Table'[Start]);ALLEXCEPT('Table';'Table'[Member]));CALCULATE(MAX('Table'[END]);ALLEXCEPT('Table';'Table'[Member]));DAY)

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Count Distinct Dates Between Start Date and End Date Columns

bricohen1 — Thu, 09 Jan 2020 18:24:09 GMT

az38 thanks for working on it. Using DATEDIFF is a good idea, but it's not quite right.

With the above example,

Member1 = 6.

Member2 = 3.

Brian

Re: Count Distinct Dates Between Start Date and End Date Columns

Mariusz — Thu, 09 Jan 2020 18:24:58 GMT

Hi bricohen1

Try something like this.

Measure = CALCULATE( DATEDIFF( MIN( 'Table'[Start] ), MAX( 'Table'[End] ), DAY ), ALL( 'Table' ), VALUES( 'Table'[Member] ) )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Re: Count Distinct Dates Between Start Date and End Date Columns

az38 — Thu, 09 Jan 2020 18:27:32 GMT

So, bricohen1

Measure = DATEDIFF(CALCULATE(MIN('Table'[Start]);ALLEXCEPT('Table';'Table'[Member]));CALCULATE(MAX('Table'[END]);ALLEXCEPT('Table';'Table'[Member]));DAY)+1

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Count Distinct Dates Between Start Date and End Date Columns

bricohen1 — Thu, 09 Jan 2020 18:53:39 GMT

az38 Mariusz

Thanks guys.

The problem is, the dates may not be consecutive.

Let's say I have this:

	Start	End
member3	1/1/2019	1/3/2019
member3	1/1/2019	1/4/2019
member3	12/6/2019	12/8/2019

Here, the answer is 7.

7 consists of: 1/1, 1/2, 1/3, 1/4, 12/6, and 12/7, and 12/8.

Re: Count Distinct Dates Between Start Date and End Date Columns

az38 — Thu, 09 Jan 2020 19:19:01 GMT

bricohen1

try to create a table

Table Calendar = ADDCOLUMNS( crossjoin(calendar(min('Table'[Start]);max('Table'[End]));distinct('Table'[Member])); "is in day";if(calculate(count('Table'[Member]);filter('Table'; 'Table'[Start]<=[Date] && 'Table'[End] >= [Date] && EARLIER('Table'[Member])=[Member]))>0;1;0) )

then just summarize it:

Table Total = summarize('Table Calendar';'Table Calendar'[Member];"Count";SUM('Table Calendar'[is in day]))

do not hesitate to give a kudo to useful posts and mark solutions as solution