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

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

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.