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; DATE USERID CLOCK 25/02/2020 1234 08:00:00 25/02/2020 1234 ...
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.

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.

      • pedanticpad's avatar
        pedanticpad
        Helper II
        6 years ago

        v-alq-msft that worked perfectly!  Thanks so much for your help.