Calculating days between dates in same column

[Nepal101]

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
10/26/2023	123	null	0 (because the salesperson is not assigned)	0
10/27/2023	123	A	1	1
10/30/2023	123	A	3	4
11/6/2023	123	A	7	11
11/7/2023	1213	a	1	12

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 

[danextian]

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]

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]

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]

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]

Hi @Nepal101 

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

[Nepal101]

Sorry about the confusion, 
I have used what you have provided to me to some extent to get the result. I appreciate your time. 
Thank you 

[Nepal101]

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]

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 ?