Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Time and attendance calculation over multiple rows with variables.

Hi all, i am new to Power BI and this is my first question on here.   I am writing a time and attendance report and i need to do a calculation of the time between clock in and clock out.  However, ...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can try to use the following measure to meet your requirement:

    TotolMinutes = 
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[WorkerPersonnelNumber],
        "Total Time",
        SUMX (
            'Table',
            IF (
                [JournalRegistrationType] = "SignOut",
                VAR ti = [StartDateTime]
                RETURN
                    DATEDIFF (
                        CALCULATE (
                            MAX ( 'Table'[StartDateTime] ),
                            FILTER (
                                'Table',
                                'Table'[JournalRegistrationType] = "SignIn"
                                    && 'Table'[StartDateTime] < ti
                            )
                        ),
                        ti,
                        MINUTE
                    ),
                BLANK ()
            )
        )
    ),
    [Total Time]
)

     

     


    If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared.

     


    Best regards,

     

v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


Best regards,

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi v-lid-msft,

 

Apologies for the delay, i have been out for a while.

 

I managed to come up with a solution which works for multiple areas, however, based on a new addition there is now a variable that has me stuck. 

 

Really appreciate your help, that has helped on other reports i am writing.

 

Thank you,

 

Steven