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

Hello There,

I have the following scenario of data.

My Output should look like the following:

 

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

 

Thanks in advance

wilson_smyth's avatar
wilson_smyth
Post Patron
7 years ago

hi Anonymous 

 

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:

 

 

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