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

 

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

3 Replies

  • amtrakrob's avatar
    amtrakrob
    Frequent Visitor
    4 years ago

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

  • 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!