Days Gap For Each Group

[Kumar11109]

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, 

[Ashish_Mathur]

Hi,

Show the result in a simple Table.

[Kumar11109]

@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 · Accepted solution]

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.

[jszlm]

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

[Anonymous]

Hi @Kumar11109,

Do you wish to calculate days between Latest and Earliest  Session Date for each group?

[Kumar11109]

No I want to calculate difference between each date for each group.