Forum Discussion

pedanticpad's avatar
pedanticpad
Helper II
6 years ago
Solved

Assign Department from Audit Table

Hi,

 

I have employee data from a time management system with clock in and out per day laid out in a table like the below;

DATEUSERIDCLOCK
25/02/2020123408:00:00
25/02/20201234

17:00:00

 

There is a Department and CostCode table but that only holds the current Department and CostCode, nothing historic.  There is an audit table laid out like the below.

CHANGEDATEUSERIDCHANGETYPENEWVALUE
01/01/20191234DEPARTMENTWarehouse
01/05/20191234DEPARTMENTStores
01/05/20191234COSTCODEStore1
25/02/20201234DEPARTMENTShipping
25/02/20201234COSTCODEDock1

 

What I need to be able to do is assign the cost code of the time to the clock in/out data, like the below.

DATEUSERIDCLOCKDEPARTMENTCOSTCODE
24/02/2020123409:00:00StoresStore1
24/02/2020123418:00:00StoresStore1
25/02/2020123408:00:00ShippingDock1
25/02/2020123417:00:00ShippingDock1

 

Any thoughts?

 

Thanks in advance!

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, pedanticpad 

     

    Based on your data, you may create two calculated columns in 'Clock' table as follows.

     

    COSTCODE = 
CALCULATE (
    MAX ( 'Audit'[NewValue] ),
    FILTER (
        'Audit',
        'Audit'[UserID] = 'Clock'[UserID]
            && 'Audit'[ChangeType] = "COSTCODE"
            && 'Audit'[ChangeDate]
                = CALCULATE (
                    MAX ( 'Audit'[ChangeDate] ),
                    FILTER (
                        'Audit',
                        'Audit'[ChangeDate] <= 'Clock'[Date]
                            && 'Audit'[UserID] = 'Clock'[UserID]
                    )
                )
    )
)

DEPARTMENT = 
CALCULATE (
    MAX ( 'Audit'[NewValue] ),
    FILTER (
        'Audit',
        'Audit'[UserID] = 'Clock'[UserID]
            && 'Audit'[ChangeType] = "DEPARTMENT"
            && 'Audit'[ChangeDate]
                = CALCULATE (
                    MAX ( 'Audit'[ChangeDate] ),
                    FILTER (
                        'Audit',
                        'Audit'[ChangeDate] <= 'Clock'[Date]
                            && 'Audit'[UserID] = 'Clock'[UserID]
                    )
                )
    )
)

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, pedanticpad 

     

    Based on your description, I assumed that two more rows for 5/1/2019 are needed and  I created data as follows.

    Employee data:

    Department and CostCode:

     

    You may duplicate query 'Employee data' and rename it as 'ResultTable'. Then you can go to 'Home' ribbon, click 'Merge Queries', configure as follows.

     

    Then you need to expand the last column, remove the unrelated columns and rename other columns.

     

    Finally you may make 'ChangeType' column selected, go to 'Transform' ribbon, click 'Pivot Column' and configure as below. You will get the result.

     

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • pedanticpad's avatar
      pedanticpad
      Helper II
      6 years ago

      Hi v-alq-msft,

       

      When I perform the pivot I get the below error;

       

       

      There are alot of NULL values in the CHANGETYPE and NEWVALUE fields.  I think the merge is only pulling in the data for an exact match and the other dates for each user is left as NULL.

       

      I need to apply the department and cost code to every clock date so that for every clock before 05/01/2019 Department = Stores and CostCode = Store1, then for every clock from 02/25/2020 Department =Shipping and CostCode = Dock1.  Any advice?

       

      Thanks!

      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        6 years ago

        Hi, pedanticpad 

         

        I wonder the result of the last step before pivotting. Here is my result before pivotting.

         

        If you have null in one column, you can click 'more options', choose what you want to filter.

         

        Best Regards

        Allan

         

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    I did not get your logic to get dates in last table. But 2nd table can be tranposed like this 

    Table = SUMMARIZE(Sheet1,Sheet1[CHANGEDATE],Sheet1[USERID],"Department",maxx(FILTER(Sheet1,Sheet1[CHANGETYPE]="DEPARTMENT"),Sheet1[NEWVALUE]),"Cost Code",maxx(FILTER(Sheet1,Sheet1[CHANGETYPE]="COSTCODE"),Sheet1[NEWVALUE]))

     

    • pedanticpad's avatar
      pedanticpad
      Helper II
      6 years ago

      Hi amitchandak / v-alq-msft,

       

      Sorry, I must not be explaining myself properly.

       

      See below tables (note only 1 UserID used as an example, but there are actually about 300 employees).  The Audit Table only records the date a change is made, but I need to assign the Department and CostCode to every clock record per the Desired Result Table below.

       

      Clock Table 
      DATEUSERIDCLOCK
      01/02/2020123409:00:00
      01/02/2020123417:00:00
      02/02/2020123409:00:00
      02/02/2020123417:00:00
      03/02/2020123409:00:00
      03/02/2020123417:00:00
      04/02/2020123409:00:00
      04/02/2020123417:00:00
      05/02/2020123409:00:00
      05/02/2020123417:00:00
      06/02/2020123409:00:00
      06/02/2020123417:00:00
      07/02/2020123409:00:00
      07/02/2020123417:00:00
      08/02/2020123409:00:00
      08/02/2020123417:00:00

       

      Audit Table   
      CHANGEDATEUSERIDCHANGETYPENEWVALUE
      01/02/20201234DEPARTMENTStores
      01/02/20201234COSTCODEStore1
      06/02/20201234DEPARTMENTShipping
      06/02/20201234COSTCODEDock1

       

      Desired Result Table   
      DATEUSERIDCLOCKDEPARTMENTCOSTCODE
      01/02/2020123409:00:00StoresStore1
      01/02/2020123417:00:00StoresStore1
      02/02/2020123409:00:00StoresStore1
      02/02/2020123417:00:00StoresStore1
      03/02/2020123409:00:00StoresStore1
      03/02/2020123417:00:00StoresStore1
      04/02/2020123409:00:00StoresStore1
      04/02/2020123417:00:00StoresStore1
      05/02/2020123409:00:00StoresStore1
      05/02/2020123417:00:00StoresStore1
      06/02/2020123409:00:00ShippingDock1
      06/02/2020123417:00:00ShippingDock1
      07/02/2020123409:00:00ShippingDock1
      07/02/2020123417:00:00ShippingDock1
      08/02/2020123409:00:00ShippingDock1
      08/02/2020123417:00:00ShippingDock1

       

      Hopefully that makes more sense?

      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        6 years ago

        Hi, pedanticpad 

         

        Based on your data, you may create two calculated columns in 'Clock' table as follows.

         

        COSTCODE = 
CALCULATE (
    MAX ( 'Audit'[NewValue] ),
    FILTER (
        'Audit',
        'Audit'[UserID] = 'Clock'[UserID]
            && 'Audit'[ChangeType] = "COSTCODE"
            && 'Audit'[ChangeDate]
                = CALCULATE (
                    MAX ( 'Audit'[ChangeDate] ),
                    FILTER (
                        'Audit',
                        'Audit'[ChangeDate] <= 'Clock'[Date]
                            && 'Audit'[UserID] = 'Clock'[UserID]
                    )
                )
    )
)

DEPARTMENT = 
CALCULATE (
    MAX ( 'Audit'[NewValue] ),
    FILTER (
        'Audit',
        'Audit'[UserID] = 'Clock'[UserID]
            && 'Audit'[ChangeType] = "DEPARTMENT"
            && 'Audit'[ChangeDate]
                = CALCULATE (
                    MAX ( 'Audit'[ChangeDate] ),
                    FILTER (
                        'Audit',
                        'Audit'[ChangeDate] <= 'Clock'[Date]
                            && 'Audit'[UserID] = 'Clock'[UserID]
                    )
                )
    )
)

         

         

        Result:

         

        Best Regards

        Allan

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.