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,

 

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