Forum Discussion

amtrakrob's avatar
amtrakrob
Frequent Visitor
4 years ago
Solved

Date Difference between rows

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 Compl...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
    amtrakrob
    4 years ago

    PERFECT!!!!!!
    Thank you!

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
amtrakrob
Frequent Visitor
4 years ago

PERFECT!!!!!!
Thank you!