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



 

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)
     

13 Replies

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

    • villa1980's avatar
      villa1980
      Resolver II
      1 year ago

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.

         

         

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

    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's avatar
      villa1980
      Resolver II
      1 year ago

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

        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