Forum Discussion

Kumar11109's avatar
Kumar11109
Helper IV
8 years ago
Solved

Days Gap For Each Group

Hi All,    I want to find the gap between dates for each group. Sample data can be found here.    Data   How will be able to do it.    Thank You,   
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    This is the calculated column formula i wrote.  You may download my solution file from here.

     

    =if(ISBLANK(CALCULATE(MAX(Data[Session Date]),FILTER(Data,Data[GroupId]=EARLIER(Data[GroupId])&&Data[Session Date]<EARLIER(Data[Session Date])))),BLANK(),Data[Session Date]-CALCULATE(MAX(Data[Session Date]),FILTER(Data,Data[GroupId]=EARLIER(Data[GroupId])&&Data[Session Date]<EARLIER(Data[Session Date]))))

     

    Hope this helps.

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Show the result in a simple Table.

Kumar11109's avatar
Kumar11109
Helper IV
8 years ago

Ashish_Mathur, Do you mean the required result?  If yes, its already in the  sample data set. Remember this is just for three groups. Actual data size is much bigger. 

 

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    This is the calculated column formula i wrote.  You may download my solution file from here.

     

    =if(ISBLANK(CALCULATE(MAX(Data[Session Date]),FILTER(Data,Data[GroupId]=EARLIER(Data[GroupId])&&Data[Session Date]<EARLIER(Data[Session Date])))),BLANK(),Data[Session Date]-CALCULATE(MAX(Data[Session Date]),FILTER(Data,Data[GroupId]=EARLIER(Data[GroupId])&&Data[Session Date]<EARLIER(Data[Session Date]))))

     

    Hope this helps.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Tried this way,

      1. Create a new column named Rank,

      Rank =
      VAR d=Table1[Session Date].[Date]
      VAR c=Table1[GroupId]
      RETURN
      CALCULATE(RANK.EQ(d,Table1[Session Date].[Date],ASC),FILTER(ALL(Table1),Table1[GroupId]=c))

       

      2. Create a new column named DateDiff,

      DateDiff =
      VAR d=Table1[Session Date].[Date]
      VAR r=Table1[Rank]
      VAR g=Table1[GroupId]
      RETURN
      CALCULATE(DATEDIFF(LOOKUPVALUE(Table1[Session Date],Table1[GroupId],g,Table1[Rank],r-1),d,DAY))