Forum Discussion
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 ...
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.
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.
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])))ReturnIf ('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.