Count Distinct Dates Between Start Date and End Date Columns

[bricohen1]

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 

[Mariusz]

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.

[az38]

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

[bricohen1]

@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

[az38]

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

[bricohen1]

@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. 

[az38 · Accepted solution]

@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

LinkedIn