Forum Discussion

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 Date ClientName salesperson Days Between Running total days or sum days ...

Super User

2 years ago

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
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
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
  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.
  
  Days diff and running total.pbix26 KB
  - Nepal101
    Helper III
    2 years ago
    Hello danextian
    Thank you so much for your insight.
    I have one more question is there a way to coun the days when the sales person is assigned instead of showing 0days
    as the sales person was assigned on that paticular day I need to count that day as one to get the correct value.