Sales VS Attendance

[gkalandadze]

hello,

got time attendance machine and sales and I would like to see which employee was in office during the particular sale.

here is screen how i see it. 

[CheenuSing]

Hi @gkalandadze 

There is overlap of timing between the two employees.

So do you want show the names of both the sales person at the time of sale ??

Cheers

CheenuSing

[gkalandadze]

Exactly

[CheenuSing]

Hi @gkalandadze ,

Here are the steps I followed

1. In the Attendance Table set the IN and OUT as TIME(HH:MM)

2. Sorted the Attendance Table by Employee

3. In the Sales Table set the TIme as TIME(HH:MM)

Added a calculated Column SalesMen  In SalesTable as
Salesmen =
VAR CurrentTime =
    FORMAT ( Sales[Time], "HH:MM" )
VAR SalesPerson =
    CALCULATETABLE (
        SUMMARIZE (
            UNION (
                CALCULATETABLE (
                    SUMMARIZE ( Attendance, Attendance[Employee] ),
                    FORMAT ( ( Attendance[IN] ), "HH:MM" ) >= CurrentTime
                ),
                CALCULATETABLE (
                    SUMMARIZE ( Attendance, Attendance[Employee] ),
                    CurrentTime <= FORMAT ( ( Attendance[OUT] ), "HH:MM" )
                )
            ),
            [Employee]
        )
    )
RETURN
    CONCATENATEX ( SalesPerson, [Employee], "," )

Atttached the Pbix

Cheers

CheenuSing

[gkalandadze]

thanks! 

 @CheenuSing 

but seem to be a little error. George shouldn't be in sales before 11:20 (time he came) and in your list he appears from the start (((

[CheenuSing · Accepted solution]

@gkalandadze ,

Sorry about that

Change the calculated column as under

Salesmen =

VAR CurrentTime =

    FORMAT ( Sales[Time], "HH:MM" )

VAR SalesPerson =

                CALCULATETABLE (

                    SUMMARIZE ( Attendance, Attendance[Employee] ),

                     CurrentTime >=FORMAT ( ( Attendance[IN] ), "HH:MM" ),

                    CurrentTime <=FORMAT ( ( Attendance[OUT] ), "HH:MM" )

                )

RETURN

    CONCATENATEX ( SalesPerson, [Employee], "," )

Let me know if it works.

Cheers

CheenuSing