Get Datediff by group

Question

Hi allI'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.&nbsp;Here's the example of what I want to do.&nbsp;id&nbsp; &nbsp; &nbsp; &nbsp;Date1&nbsp; &nbsp; &nbsp; &nbsp; 01/01/20191&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;01/20/20192&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;01/10/20191&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;02/20/20192&nbsp; &nbsp; &nbsp; &nbsp;01/15/20192&nbsp; &nbsp; &nbsp; &nbsp;01/20/2019&nbsp;So I want to calculate two new columns which are these ones:&nbsp;id&nbsp; &nbsp; &nbsp; &nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Next Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Diff1&nbsp; &nbsp; &nbsp; &nbsp; 01/01/2019&nbsp; &nbsp; &nbsp;01/20/2019&nbsp; &nbsp; &nbsp; &nbsp;191&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;01/20/2019&nbsp; &nbsp; &nbsp;02/20/2019&nbsp; &nbsp; &nbsp; &nbsp;312&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;01/10/2019&nbsp; &nbsp; &nbsp;01/15/2019&nbsp; &nbsp; &nbsp; &nbsp;51&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;02/20/2019&nbsp; &nbsp; &nbsp; *Today()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;lots of days*2&nbsp; &nbsp; &nbsp; &nbsp;01/15/2019&nbsp; &nbsp; &nbsp; 01/20/2019&nbsp; &nbsp; &nbsp; &nbsp;52&nbsp; &nbsp; &nbsp; &nbsp;01/20/2019&nbsp; &nbsp; &nbsp; &nbsp;*Today()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;lots of days*&nbsp;For now I have this calculated column:&nbsp;&nbsp;DateDiff Column = VAR PreviousDate = Table1[Date]&nbsp;VAR CurrentDate = CALCULATE (LASTDATE ( Table1[Date] ),ALLEXCEPT ( Table1, Table1[Id] ),Table1[Date] &lt; EARLIER ( Table1[Date]) )RETURNIF ( ISBLANK ( CurrentDate ), DATEDIFF ( CurrentDate, TODAY(), DAY ), DATEDIFF ( CurrentDate, PreviousDate, DAY ) )&nbsp;but it gives me the result out of fhase.&nbsp;May you help me whith this, please.

v-easonf-msft · Accepted Answer

Hi,&nbsp;AlexGlz&nbsp;If I didn't misunderstand your meaning ,you can try the following steps:Sort the table by “id” and “ date”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 )Create calculate columns “Next Date01” and “Datediff” like that :&nbsp;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
​&nbsp;&nbsp;Here’s a sample I made:&nbsp;url:https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/ETgEEy8xRn5IiQrnvkI__3UBqPFpMsyG6OmkGxjl5XUKRA?e=DKkaQn&nbsp;&nbsp;Best Regards,EasonCommunity Support Team _ Eason FangIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Get Datediff by group

2 Replies

Related content

DATEDIFF DAX

Fabric & Azure Data User Group Program Requirements & Benefits

Community User Group Experience

DATEDIFF by group

User Group Leader Get Started Guide