cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Get Datediff by group

Hi all

I'm trying to do a Gantt Chart and I'm almost there, but I still need to get a date difference between some dates to get the chart as I want.

Here's the example of what I want to do.

id       Date

1        01/01/2019

1        01/20/2019

2        01/10/2019

1        02/20/2019

2       01/15/2019

2       01/20/2019

So I want to calculate two new columns which are these ones:

id       Date                Next Date         Diff

1        01/01/2019     01/20/2019       19

1        01/20/2019     02/20/2019       31

2        01/10/2019     01/15/2019       5

1        02/20/2019      *Today()           lots of days*

2       01/15/2019      01/20/2019       5

2       01/20/2019       *Today()           lots of days*

For now I have this calculated column:

DateDiff Column =
VAR PreviousDate = Table1[Date]

VAR CurrentDate =
CALCULATE (
LASTDATE ( Table1[Date] ),
ALLEXCEPT ( Table1, Table1[Id] ),
Table1[Date] < EARLIER ( Table1[Date]) )
RETURN
IF ( ISBLANK ( CurrentDate ), DATEDIFF ( CurrentDate, TODAY(), DAY ), DATEDIFF ( CurrentDate, PreviousDate, DAY ) )

but it gives me the result out of fhase.

May you help me whith this, please.
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: Get Datediff by group

Hi, @AlexGlz

If I didn't misunderstand your meaning ,you can try the following steps:

1. Sort the table by “id” and “ date”
2. Insert a index column into the table to help you create DAX.( You can right-click to hide the created index column if you don't need to display it )
3. Create calculate columns “Next Date01” and “Datediff” like that :

``````Next Date01 =
var ind='Date'[Index]+1
var nextdate=CALCULATE(MAX('Date'[Date]),FILTER('Date','Date'[Index]=ind))
var nextid=CALCULATE(MAX('Date'[id]),FILTER('Date','Date'[Index]=ind))
var val=IF('Date'[id]=nextid,nextdate,TODAY())
return
val
​``````
``````DateDiff =
var days=DATEDIFF('Date'[Date],'Date'[Next Date01],DAY)
RETURN days
​``````

Best Regards,

Eason

Community Support Team _ Eason Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Highlighted
Community Support

## Re: Get Datediff by group

Hi, @AlexGlz

If I didn't misunderstand your meaning ,you can try the following steps:

1. Sort the table by “id” and “ date”
2. Insert a index column into the table to help you create DAX.( You can right-click to hide the created index column if you don't need to display it )
3. Create calculate columns “Next Date01” and “Datediff” like that :

``````Next Date01 =
var ind='Date'[Index]+1
var nextdate=CALCULATE(MAX('Date'[Date]),FILTER('Date','Date'[Index]=ind))
var nextid=CALCULATE(MAX('Date'[id]),FILTER('Date','Date'[Index]=ind))
var val=IF('Date'[id]=nextid,nextdate,TODAY())
return
val
​``````
``````DateDiff =
var days=DATEDIFF('Date'[Date],'Date'[Next Date01],DAY)
RETURN days
​``````

Best Regards,

Eason

Community Support Team _ Eason Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

## Re: Get Datediff by group

Unfortunately for now I couldn't make the index column because I'm taking the Data from a sharepoint and I don't know if the server has issues or something but I Power BI can't autenticate my credentials, so I can't add any column in power Query...

I will try again in the next days...

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors