Get Datediff by group

[AlexGlz]

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.

[v-easonf-msft · Accepted solution]

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
​

Here’s a sample I made:

url:https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/ETgEEy8xRn5IiQrnvkI__... 

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.

[AlexGlz]

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