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 Date ClientName salesperson Days Between Running total days or sum days ...
Need Help
Show and Tell
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

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.

     

     

    • Nepal101's avatar
      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. 

    • Nepal101's avatar
      Nepal101
      Helper III
      2 years ago

      At first I created 

      test = If('MV[Salesperson]<>Blank(),'MV'[Date],Blank())
      MeasureMIN-new = CALCULATE(MIN('MV'[test]),ALLEXCEPT('MV'[Client Name]))
      Measure 3 = [MeasureMIN-new]-1 ( so that I can get 1 day when the salesperson is assigned)
      Date diff =
      var fistdate= [MeasureMIN-new]
      var Demote =
      CALCULATE(
          MAX('MV'[test]),
          Filter(
              ALLEXCEPT('MV'[Client Name]),
              'MV'[test])))

              Return
             
           If (
              'MV'[test]=fistdate,
              DATEDIFF([Measure 3],'MV'[test],Day),
              DATEdiff(Demote,'MV'[test],DAY))

       

      Is there a way to count the days rolling over meaning for 11/6/2023 =1 on 11/7/2023 it should show 2  and 11/8/2023  it should show 3  and for 11/14/2023 it should show 9 for that row of date. is that possible. I can't upload the file so hope this helps what I have explained. 

      • danextian's avatar
        danextian
        Super User
        2 years ago

        Hi Nepal101 

        I would suggest you provided enough information and sample data that covers your use case instead of just  bits and pieces.