Forum Discussion

gkalandadze's avatar
gkalandadze
Frequent Visitor
7 years ago
Solved

Sales VS Attendance

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. 
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
gkalandadze
Frequent Visitor
7 years ago

Exactly

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      gkalandadze
      Frequent Visitor
      7 years ago

      thanks! 

       Anonymous 

      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 (((

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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