Forum Discussion
Solved
datediff between rows in a group
I have data in a table that can be grouped by a column, in this case, grouping id. I want to calculate the difference between dates in each group. Ive attempted it using a calculated column, but ...
I believe this should work...
DateDiff Column =
VAR PreviousDate =
CALCULATE (
LASTDATE ( Sheet1[date] ),
ALLEXCEPT ( Sheet1, Sheet1[GroupID] ),
Sheet1[date] < EARLIER ( Sheet1[date] )
)
VAR CurrentDate = Sheet1[date]
RETURN
IF ( ISBLANK ( PreviousDate ), 0, DATEDIFF ( PreviousDate, CurrentDate, DAY ) )Hope this helps! :smileyhappy:
EDIT: This should work as a Measure
DateDiff Measure = VAR PreviousDate = CALCULATE ( LASTDATE ( Sheet1[date] ), ALLEXCEPT ( Sheet1, Sheet1[GroupID] ), FILTER ( ALLSELECTED ( Sheet1[date] ), Sheet1[date] < MIN ( Sheet1[date] ) ) ) VAR CurrentDate = MIN ( Sheet1[date] ) RETURN IF ( ISBLANK ( PreviousDate ), 0, DATEDIFF ( PreviousDate, CurrentDate, DAY ) )
I believe this should work...
DateDiff Column =
VAR PreviousDate =
CALCULATE (
LASTDATE ( Sheet1[date] ),
ALLEXCEPT ( Sheet1, Sheet1[GroupID] ),
Sheet1[date] < EARLIER ( Sheet1[date] )
)
VAR CurrentDate = Sheet1[date]
RETURN
IF ( ISBLANK ( PreviousDate ), 0, DATEDIFF ( PreviousDate, CurrentDate, DAY ) )
Hope this helps! :smileyhappy:
EDIT: This should work as a Measure
DateDiff Measure =
VAR PreviousDate =
CALCULATE (
LASTDATE ( Sheet1[date] ),
ALLEXCEPT ( Sheet1, Sheet1[GroupID] ),
FILTER ( ALLSELECTED ( Sheet1[date] ), Sheet1[date] < MIN ( Sheet1[date] ) )
)
VAR CurrentDate =
MIN ( Sheet1[date] )
RETURN
IF ( ISBLANK ( PreviousDate ), 0, DATEDIFF ( PreviousDate, CurrentDate, DAY ) )
That helps a great deal, thanks!
Am i correct in saying your solution ignores the order_1 column completely for ordering, instead relying on the order of dates using LASTDATE to get the last date for the group in question?
Just want to be sure i understand how it works.