Forum Discussion

villa1980's avatar
villa1980
Resolver II
1 year ago
Solved

Time Duration between rows

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 ...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    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)
     
rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

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.