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 ,

 

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,