cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper IV

## 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,

1 ACCEPTED SOLUTION
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
6 REPLIES 6
Super User

Hi,

Show the result in a simple Table.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper IV

@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.

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Employee

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
Not applicable

Hi @Kumar11109,

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

Helper IV

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors