Forum Discussion

bricohen1's avatar
bricohen1
Frequent Visitor
6 years ago
Solved

Count Distinct Dates Between Start Date and End Date Columns

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/2...
  • az38's avatar
    az38
    6 years ago

    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

az38's avatar
az38
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
bricohen1
Frequent Visitor
6 years ago

az38 Mariusz 

 

Thanks guys. 

The problem is, the dates may not be consecutive. 

Let's say I have this: 

 StartEnd
member31/1/20191/3/2019
member31/1/20191/4/2019
member312/6/201912/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's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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