Forum Discussion
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 not succeeded. Would appreciate some expertise. Note, a measure would be equally welcome, im not wedded to the idea of a calc column, it just seemed easier due to context.
There is a sample dataset in a pbix at this link and a screenshot of the dataset below. The columns are:L
GroupID: This is the column that indicates which group the row belongs.
Date: date of the row.
order_1: order of the row within the group.
expected daydiff: the expected output from calc col or measure.
Attempt_2: My attempt that currently does not work.
attempt2 = VAR GrpID = Sheet1[GroupID] VAR PrevDate = Sheet1[date].[Date] VAR orderingVal = Sheet1[order_1] return calculate(DATEDIFF(PrevDate, MAX(Sheet1[date]),DAY), filter(all(Sheet1),(GrpID = Sheet1[GroupID]) && (Sheet1[order_1] > orderingVal)))
Thank you for any expertise and advice.
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 ) )
10 Replies
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.Okay so lets go through what the COLUMN formula actually does (the logic is the same behind the Measure formula)
Specifically how we find the PreviousDate as all else I believe is pretty straightforward
So on each row the first thing we'll do is look at the GroupID on that row
Then we'll look to find the last date that is before (less than) the date that that is on the row we are on
(and don't forget this would be only for data that has the same GroupID as the GroupID on that row)
Therefore there's no need to look at the order_1 column - the formula takes care of this.
For example imagine we are looking at the last row in your sample data
First we'll look at the GroupID on that row which is 1
then we'll look for the Last date that is less than Feb 2, 17 (the date on the current row) only for data that has a GroupID of 1
and that would be Jan 26, 17. That's how the PreviousDate will be calculated on each row.
Hope this helps! :smileyhappy:
