Time Duration between rows

[villa1980]

I am trying to look at the duration between the end date and next start date for each STARTDATE category.
I am using the below DAX and is coming out with some weird results which I am struggling to understand. It should be saying 60 or 90.
Results are below and the DAX I am trying to incorporate.
Thanks

NextRow_CALC =

VAR _CurrentTaskTime = CALCULATE(SELECTEDVALUE(AVAILABLE_DIARY_DATE_inc_Start_Date[STARTDATE]))

VAR _PreviousTaskTime =

CALCULATE(

   MAX(AVAILABLE_DIARY_DATE_inc_Start_Date[STARTDATE]),

   ALLEXCEPT(AVAILABLE_DIARY_DATE_inc_Start_Date,AVAILABLE_DIARY_DATE_inc_Start_Date[STARTDATE]),

   AVAILABLE_DIARY_DATE_inc_Start_Date[STARTDATE]<_CurrentTaskTime

)

VAR _timeDifference_IN_Minutes =

IF(ISBLANK(_PreviousTaskTime),

0,

DATEDIFF(_PreviousTaskTime,_CurrentTaskTime,MINUTE)

)

RETURN

_timeDifference_IN_Minutes

[ryan_mayu]

@villa1980 

all your end date is the same as the next start date. What's the expected output based on the screenshot you provided?

[villa1980]

Hi Ryan,
 There aren't although there are not many you can see on the 4th the end date is 15:00 and the next start date is 15:30, I need this to be flagged for another calculation so that is why I am trying to return the gap duration

[ryan_mayu]

@villa1980 

is this what you want?

Measure =

VAR _end=maxx(FILTER(ALL('Table'),'Table'[CENTERID]=max('Table'[CENTERID])&&'Table'[ENDDATE]<max('Table'[ENDDATE])),'Table'[ENDDATE])

return DATEDIFF(_end,max('Table'[STARTDATE]),MINUTE)

pls see the attachment below

[villa1980]

ooh getting closer, these is what it returns. Not sure why it has -30 on the other rows
I did attach a link to the pbix, maybe it is that?

[ryan_mayu · Accepted solution]

@villa1980 

since you have the index column , you can try this

Measure =

var _end=maxx(FILTER(all('Table'),'Table'[BAYDESC]=max('Table'[BAYDESC])&&'Table'[Index]=max('Table'[Index])-1),'Table'[ENDDATE])

return DATEDIFF(_end,max('Table'[STARTDATE]),MINUTE)

[villa1980]

wow, you have hit the nail on the head with this, works an absolute treat, thank-you so much.
I have a couple of questions if you don't mind...

1) If I wanted to do this for start date as a group, how would I add that?

2) In simple speak could you explain what it does 🙂

[ryan_mayu]

@villa1980 

what do you man by start date as a group? It is a table visual. Usually it will automatically group the column data which is text or date format.

[villa1980]

That is fine, think I was just trying to over think it, I just need to highlight if is less than 60 and then calculate form that, is fine.

Thank-you once again

[ryan_mayu]

you are welcome

[rajendraongole1]

Hi @villa1980 - You can rewrite the measure using TOPN

NextRow_CALC =
VAR _CurrentTaskTime = SELECTEDVALUE(AVAILABLE_DIARY_DATE_inc_Start_Date[STARTDATE])
VAR _NextTaskTime =
CALCULATE(
MIN(AVAILABLE_DIARY_DATE_inc_Start_Date[STARTDATE]),
FILTER(
ALL(AVAILABLE_DIARY_DATE_inc_Start_Date),
AVAILABLE_DIARY_DATE_inc_Start_Date[STARTDATE] > _CurrentTaskTime
)
)
VAR _TimeDifference =
IF(
ISBLANK(_NextTaskTime),
BLANK(),
DATEDIFF(_CurrentTaskTime, _NextTaskTime, DAY) -- Use DAY, or adjust as needed
)
RETURN
_TimeDifference

Hope this works. 

[villa1980]

Thank-you for the response, it is returning the below which is still not correct

[v-yajiewan-msft]

Hi @villa1980 ， hello ryan_mayu and rajendraongole1, thank you for your prompt reply!

Per my test, I could use the following measure to calculate the differences between startdate as shown below:

NextTaskDuration = 
VAR _CurrentTaskStart = SELECTEDVALUE('Table'[STARTDATE])
VAR _NextTaskStart =
    CALCULATE(
        MIN('Table'[STARTDATE]),
        FILTER(
            ALL('Table'),
            'Table'[STARTDATE]> _CurrentTaskStart
        )
    )
VAR _TimeDifferenceInMinutes =
    IF(
        ISBLANK(_NextTaskStart),
        0,
        DATEDIFF(_CurrentTaskStart, _NextTaskStart, MINUTE)
    )
RETURN
    _TimeDifferenceInMinutes

Result:

You could also provide more details about your issue or upload a sample PBIX file for better troubleshooting.

Best regards,

Joyce

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

[villa1980]

This is strange, I have the exact same DAX but has these strange returns. I have attached the pbix file

https://drive.google.com/file/d/1YWVZfHt1t1lodS6TxFmIt_U1Fk0js2Xb/view?usp=drive_link