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.

     

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