Forum Discussion

wilson_smyth's avatar
wilson_smyth
Post Patron
8 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
    8 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

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.