Forum Discussion

Nepal101's avatar
Nepal101
Helper III
2 years ago

Calculating days between dates in same column

Hello Everyone, 
I have a question regarding how to calculate the column or Measure that gives me days between dates

DateClientNamesalespersonDays BetweenRunning total days or sum days
10/26/2023123null0 (because the salesperson is not assigned)0
10/27/2023123A11
10/30/2023123A34
11/6/2023123A711
11/7/20231213a112

from the same column and on top of that I need to have the sum of days for each row. 
Is there a way to calculate the Total days or sum days and days between
I hope someone can help me get this result. 
Thank you in advance 


Need Help
Show and Tell

8 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi Nepal101 ,

    Assuming that the days difference is to be calculated by client name:

    Days Diff = 
VAR __PREV =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Date] < EARLIER ( 'Table'[Date] )
                && 'Table'[ClientName] = EARLIER ( 'Table'[ClientName] )
        )
    )
RETURN
    DATEDIFF ( __PREV, 'Table'[Date], DAY ) + 0

    Otherwise, remove this line:

    && 'Table'[ClientName] = EARLIER ( 'Table'[ClientName] )

     

    • Nepal101's avatar
      Nepal101
      Helper III
      2 years ago

      Hello danextian, 
      Thank you so much for your help and time. 
      is there a way to calculate the running total for the days as well please. 
      Can the days be sum up for each rows ?

    • Nepal101's avatar
      Nepal101
      Helper III
      2 years ago

      I tried to use the column as you have stated. I think there is a logic missing regarding the salesperson so if the sales person is not assigned then the day should not be count. Can we create that column accordingly. 

       

      • danextian's avatar
        danextian
        Super User
        2 years ago

        Hi Nepal101 

         

        Use this instead:

        Days Diff = 
VAR __PREV =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Date] < EARLIER ( 'Table'[Date] )
                && 'Table'[ClientName] = EARLIER ( 'Table'[ClientName] )
                && 'Table'[salesperson] <> "null" )
        )
    
RETURN
    DATEDIFF ( __PREV, 'Table'[Date], DAY ) + 0

        As to the running total, you can either use a calc column or a measure but calc column is not to be aggregated as it will show incorrect sub or grand total. Please see attached pbix for the details.