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 ) )
Sean's avatar
Sean
Community Champion
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 ) )
  • wilson_smyth's avatar
    wilson_smyth
    Post Patron
    7 years ago

    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's avatar
    Sean
    Community Champion
    7 years ago

    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! :smileyhappy:

    • 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))
    • MaggieW420's avatar
      MaggieW420
      Regular Visitor
      1 year ago

      This information was super helpful and it worked for most part, whoever the issue I am having is when the previous row and the current row are the same date, how can I adjust the measure to account for same date situations? 

      PatientIDSalesOrderNumberDateofService
      1144514091217/25/2023
      1144514110498/25/2023
      1144514110609/24/2023
      1144514913859/24/2023
      11445149468610/31/2023
      11445149468811/30/2023
      11445149469412/30/2023

       

      Here is DAX Formula I am using for my measure:

      Days Between DOS =
      VAR PreviousDate =
          CALCULATE (
              LASTDATE ( InvoiceData[DateofService] ),
              ALLEXCEPT ( InvoiceData, InvoiceData[PatientID] ),
              FILTER (
                  ALLSELECTED ( InvoiceData[DateofService] ),
                  InvoiceData[DateofService] < MIN ( InvoiceData[DateofService] )))
      VAR CurrentDate =
          MIN ( InvoiceData[DateofService] )
      RETURN
                 IF (ISBLANK ( PreviousDate ), 0, DATEDIFF ( PreviousDate, CurrentDate, DAY ))
       
      Any additional insight you have would be greatly appreciated. Thanks