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: 

 

MemberStartEnd
member11/1/20191/5/2019
member11/1/20191/5/2019
member11/1/20191/6/2019
member23/1/20193/2/2019
member23/1/20193/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 

  • 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

6 Replies

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

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

      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'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

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