cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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.

30438929408_db7e4a4eef_o

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Sean Super Contributor
Super Contributor

Re: datediff between rows in a group

@wilson_smyth

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! Smiley Happy

 

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 ) )

View solution in original post

9 REPLIES 9
Sean Super Contributor
Super Contributor

Re: datediff between rows in a group

@wilson_smyth

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! Smiley Happy

 

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 ) )

View solution in original post

Re: datediff between rows in a group

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.

Sean Super Contributor
Super Contributor

Re: datediff between rows in a group

@wilson_smyth

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! Smiley Happy

kbamarnath Frequent Visitor
Frequent Visitor

Re: datediff between rows in a group

Hello There,

I have the following scenario of data.

Capture.JPG

My Output should look like the following:

Capture1.JPG

 

I want the date difference between EndDate and the Min Date of each group.

 

Thanks in advance

Re: datediff between rows in a group

hi @kbamarnath 

 

Its better to ask new questions in new posts, otherwise , threads will be long and confusing.

Its also helpful to provide a link to a powerbi file loaded with data, to aid anyone who wishes to help.

 

I mocked up an example myself, and think this is what you are looking for:

 

Capture.JPG

 

Measure = 

var GroupMin = calculate(min(Table1[startDate]), ALLEXCEPT(Table1,Table1[group]))
var CurrentDate = min(Table1[EndDate])

return 
if(isblank(GroupMin),0, DATEDIFF(GroupMin, CurrentDate,DAY))
kbamarnath Frequent Visitor
Frequent Visitor

Re: datediff between rows in a group

Thank you for your reply.  I will take your advice regarding the post.

I found a solution and it is along the lines of your suggestion.

So, ALLExcept does the grouping here?

I am confused on where the grouping is being done here

 

Re: datediff between rows in a group

Yes, AllExcept removes All Filters from all columns, Except for the Group column.

kbamarnath Frequent Visitor
Frequent Visitor

Re: datediff between rows in a group

not sure if i am following. where is the grouping being performed?

 Thanks in advance

Re: datediff between rows in a group

the grouping is being performed by the removal of all filters, except for the one we want to group against.

This happens in the ALLEXCEPT function.

 

Powerbi groups by whatever columns are in the filter context. 
If we have groupID, startdate, enddate in the filter context, then it will have a row context for each distinct row of groupid, startdate,enddate.


As we remove all but groupid from the filtercontext, it now has a row for all distinct values in the reamining column, which is groupid.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)