Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Need help to Calculate Time duration

Hello All,

 

 

I have swipe card details table as below

 

 

Here i would like to calculate the time duration from In and out each time.

 

Here in above image we need to get the time duration from 1st I01 to 1st O01 like wise for every In and Out.

But at last I01 theres no Out, so here from that In time to current time calculate the time duration.

 

 

How can i calculate the time duration.

 

Any suggestion please

 

Mohan V

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

    Try this

     

    =
VAR OutTime =
    CALCULATE (
        MIN ( TableName[Time] ),
        FILTER (
            TableName,
            TableName[Time] > EARLIER ( TableName[Time] )
                && TableName[Employee Name] = EARLIER ( TableName[Employee Name] )
                && TableName[In/Out] = "O01"
        )
    )
VAR Endtime =
    IF ( ISBLANK ( OutTime ), NOW (), OutTime )
RETURN
    IF ( TableName[In/Out] = "I01", DATEDIFF ( TableName[Time], EndTime, MINUTE ) )

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Hi Anonymous

     

    Try this column

     

    =
VAR OutTime =
    CALCULATE (
        MIN ( TableName[Time] ),
        FILTER (
            TableName,
            TableName[Time] > EARLIER ( TableName[Time] )
                && TableName[In/Out] = "O01"
        )
    )
VAR Endtime =
    IF ( ISBLANK ( OutTime ), NOW (), OutTime )
RETURN
    IF ( TableName[In/Out] = "I01", DATEDIFF ( TableName[Time], EndTime, MINUTE ) )
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Zubair_Muhammad 

       

      I have tried what you have suggested.

       

      but it is not giving me the right output

       

      Can you please help me to get this done..

      It will help me a lot.

       

      Thank you.

      Mohan V

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        Anonymous

         

        Try this

         

        =
VAR OutTime =
    CALCULATE (
        MIN ( TableName[Time] ),
        FILTER (
            TableName,
            TableName[Time] > EARLIER ( TableName[Time] )
                && TableName[Employee Name] = EARLIER ( TableName[Employee Name] )
                && TableName[In/Out] = "O01"
        )
    )
VAR Endtime =
    IF ( ISBLANK ( OutTime ), NOW (), OutTime )
RETURN
    IF ( TableName[In/Out] = "I01", DATEDIFF ( TableName[Time], EndTime, MINUTE ) )