Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Time difference between date time values in same column based on a value in a different column

Hello, I am trying to aggregate the time a user has spent in the Receipt function scanning items in the warehouse. How would I get the output in the following table (elapsed seconds):   Assignm...
Help
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    Hi Anonymous ,

    Here is the pbix PBIX 
    I put both columns in so that you have your choice, the second column only shows in the last row of the user. The difference between the measure and the column at the high level, is that with a measure, the engine does not have row context, whereas in the table it does. (It knows which row it is operating on.) Therefore it was just a matter of dropping MAX().


    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

     

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

Hi Anonymous 
What is the difference between the two tables?
Nathaniel

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi Anonymous ,

    If I understand correctly this is what you are looking as an output? 
    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel


     

     

     

    lapse Secs Calc = 
VAR _curTime =
    MAX ( TimeElapse[Date Time Start] )
VAR _pasttime =
    CALCULATE (
        MAX ( TimeElapse[Date Time Start] ),
        TimeElapse[Date Time Start] < _curTime,
        ALLEXCEPT ( TimeElapse, TimeElapse[User], TimeElapse[Function] )
    )
VAR _dif =
    DATEDIFF ( _pasttime, _curTime, SECOND )
RETURN
    _dif

     



     

    • Nathaniel_C's avatar
      Nathaniel_C
      Community Champion
      6 years ago

      Hi Anonymous 
      Or with zeros.
      Let me know if you have any questions.

      If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
      Nathaniel

       

      Elapse Secs Calc = 
VAR _curTime =
    MAX ( TimeElapse[Date Time Start] )
VAR _pasttime =
    CALCULATE (
        MAX ( TimeElapse[Date Time Start] ),
        TimeElapse[Date Time Start] < _curTime,
        ALLEXCEPT ( TimeElapse, TimeElapse[User], TimeElapse[Function] )
    )
VAR _dif =
    IF(ISBLANK(_pasttime), 0 ,DATEDIFF ( _pasttime, _curTime, SECOND ))
RETURN
    _dif

       

      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion
        6 years ago

        Hi Anonymous ,

        Here is the solution with only the final value showing for each User and Function.

         

        Only latest value = 
VAR _calc = [Cummulative]
VAR _maxDate =
    CALCULATE (
        MAX ( TimeElapse[Date Time Start] ),
        ALLEXCEPT ( TimeElapse, TimeElapse[User], TimeElapse[Function] )
    )
RETURN
    IF ( MAX ( TimeElapse[Date Time Start] ) = _maxDate, [Cummulative], 0 )


        Let me know if you have any questions.

        If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
        Nathaniel

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Nathaniel_C ,

      I just imported my example data into a new file and ran your function on it but my results do not match to what you had.

       

       

      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion
        6 years ago

        Hi Anonymous ,

        Try it as a measure, not a calculated column.  Looking at your first picture that is what i built it for. Put the first 5 columns on a table, then add the measure.
        Let me know if you have any questions.

        If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
        Nathaniel