How to calculate days between the end date and the next start date?

[Anonymous]

 

So above is an example of my dataset. First I arranged all the names on alphabetic order and column test end date is also ordered from new to old.
I arranged these in Power Query. What I would like to know is, how to calculate the days between the end date per person and the next start date per person, in days. 

So far I tried the following DAX-code:

Next time = If('Report A''s Actual'[Name]=LOOKUPVALUE('Report A''s Actual'[name], 'Report A''s Actual'[Index],'Report A''s Actual'[Index]+1), LOOKUPVALUE('Report A''s Actual'[End Date], 'Report A''s Actual'[Index],'Report A''s Actual'[Index]+1))

Days between = DATEDIFF('Report A''s Actual'[Start Date], 'Report A''s Actual'[Test next time],DAY)

Link: (51) Calculate Break time between two consecutive Group by Rows in Power BI - YouTube

The DAX-code above doens't work, after arranging the data in Power Query. If I don't arrange the data, then the code works. 
But it can sometimes give the wrong amount of calculated days, because the dates are not arranged. 
So how can I calculate the days between the last date and the next start date per person?

Note: I am new to Power BI and English is also not my first language. Thank you. 
 

[Greg_Deckler · Accepted solution]

@Anonymous You can just subtract the dates and multiply by 1 to make sure it returns a number. You can use MTBF. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[tamerj1 · Accepted solution]

Hi @Anonymous 

please try

Days between =
VAR CurrentStarDate = 'Report As Actual'[Start Date]
VAR CurrentEndDate = 'Report As Actual'[End Date]
VAR CurrentNameTable =
    CALCULATETABLE (
        'Report As Actual',
        ALLEXCEPT ( 'Report As Actual', 'Report As Actual'[Name] )
    )
VAR DatesAfter =
    FILTER ( CurrentNameTable, 'Report As Actual'[Start Date] > CurrentStarDate )
VAR NextStartDate =
    MINX ( DatesAfter, 'Report As Actual'[Start Date] )
RETURN
    IF (
        NOT ISBLANK ( NextStartDate ),
        DATEDIFF ( CurrentEndDate, NextStartDate, DAY )
    )

[Greg_Deckler · Accepted solution]

@Anonymous You can just subtract the dates and multiply by 1 to make sure it returns a number. You can use MTBF. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous