Forum Discussion

wilson_smyth's avatar
wilson_smyth
Post Patron
7 years ago
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 ...
Need Help
  • Sean's avatar
    Sean
    7 years ago

    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! :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 ) )
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

 

wilson_smyth's avatar
wilson_smyth
Post Patron
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

     Thanks in advance

    • wilson_smyth's avatar
      wilson_smyth
      Post Patron
      7 years ago

      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.