Date Difference between rows

[amtrakrob]

Hello All...
I actually have 2 date related questions.

I have a training database. There are three columns. UserID, CourseID, CompleteDate. The database is sorted by UserID then CourseId then CompleteDate descending.  
1. I need a column that shows the number of days between each completedate.
2. I need a coulmn that shows the number of days between the completed date and MOST RECENT COMPLETE DATE.

I need the calculation to change for each couse, and then for each UserID

Thanks everyone.

[v-eqin-msft · Accepted solution]

Hi @amtrakrob ,

Please try the following formula to create columns:

Rank = RANKX(FILTER('Table',[User Id]=EARLIER('Table'[User Id]) && [Course ID]=EARLIER('Table'[Course ID])),[Complete],,DESC)

Days Between Completion = 
var _pre=CALCULATE(MAX('Table'[Complete]),FILTER('Table',[User Id]=EARLIER('Table'[User Id]) && [Course ID]=EARLIER('Table'[Course ID]) && [Rank]=EARLIER('Table'[Rank])-1))
return DATEDIFF([Complete],_pre,DAY)+0

Days Since Most Recent Training = SUMX(FILTER('Table',[User Id]=EARLIER('Table'[User Id]) && [Course ID]=EARLIER('Table'[Course ID]) && [Complete]>=EARLIER('Table'[Complete])),[Days Between Completion])

Output:

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

[v-eqin-msft · Accepted solution]

Hi @amtrakrob ,

Please try the following formula to create columns:

Rank = RANKX(FILTER('Table',[User Id]=EARLIER('Table'[User Id]) && [Course ID]=EARLIER('Table'[Course ID])),[Complete],,DESC)

Days Between Completion = 
var _pre=CALCULATE(MAX('Table'[Complete]),FILTER('Table',[User Id]=EARLIER('Table'[User Id]) && [Course ID]=EARLIER('Table'[Course ID]) && [Rank]=EARLIER('Table'[Rank])-1))
return DATEDIFF([Complete],_pre,DAY)+0

Days Since Most Recent Training = SUMX(FILTER('Table',[User Id]=EARLIER('Table'[User Id]) && [Course ID]=EARLIER('Table'[Course ID]) && [Complete]>=EARLIER('Table'[Complete])),[Days Between Completion])

Output:

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

[amtrakrob · Accepted solution]

PERFECT!!!!!!
Thank you!

[amtrakrob]

Sorry... I should have said. I'm doing this in Power BI, and then these to be columns on my visual.