Assign Department from Audit Table

[pedanticpad]

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

CHANGEDATE	USERID	CHANGETYPE	NEWVALUE
01/01/2019	1234	DEPARTMENT	Warehouse
01/05/2019	1234	DEPARTMENT	Stores
01/05/2019	1234	COSTCODE	Store1
25/02/2020	1234	DEPARTMENT	Shipping
25/02/2020	1234	COSTCODE	Dock1

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.

DATE	USERID	CLOCK	DEPARTMENT	COSTCODE
24/02/2020	1234	09:00:00	Stores	Store1
24/02/2020	1234	18:00:00	Stores	Store1
25/02/2020	1234	08:00:00	Shipping	Dock1
25/02/2020	1234	17:00:00	Shipping	Dock1

Any thoughts?

Thanks in advance!

[amitchandak]

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]

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
DATE	USERID	CLOCK
01/02/2020	1234	09:00:00
01/02/2020	1234	17:00:00
02/02/2020	1234	09:00:00
02/02/2020	1234	17:00:00
03/02/2020	1234	09:00:00
03/02/2020	1234	17:00:00
04/02/2020	1234	09:00:00
04/02/2020	1234	17:00:00
05/02/2020	1234	09:00:00
05/02/2020	1234	17:00:00
06/02/2020	1234	09:00:00
06/02/2020	1234	17:00:00
07/02/2020	1234	09:00:00
07/02/2020	1234	17:00:00
08/02/2020	1234	09:00:00
08/02/2020	1234	17:00:00

Audit Table
CHANGEDATE	USERID	CHANGETYPE	NEWVALUE
01/02/2020	1234	DEPARTMENT	Stores
01/02/2020	1234	COSTCODE	Store1
06/02/2020	1234	DEPARTMENT	Shipping
06/02/2020	1234	COSTCODE	Dock1

Desired Result Table
DATE	USERID	CLOCK	DEPARTMENT	COSTCODE
01/02/2020	1234	09:00:00	Stores	Store1
01/02/2020	1234	17:00:00	Stores	Store1
02/02/2020	1234	09:00:00	Stores	Store1
02/02/2020	1234	17:00:00	Stores	Store1
03/02/2020	1234	09:00:00	Stores	Store1
03/02/2020	1234	17:00:00	Stores	Store1
04/02/2020	1234	09:00:00	Stores	Store1
04/02/2020	1234	17:00:00	Stores	Store1
05/02/2020	1234	09:00:00	Stores	Store1
05/02/2020	1234	17:00:00	Stores	Store1
06/02/2020	1234	09:00:00	Shipping	Dock1
06/02/2020	1234	17:00:00	Shipping	Dock1
07/02/2020	1234	09:00:00	Shipping	Dock1
07/02/2020	1234	17:00:00	Shipping	Dock1
08/02/2020	1234	09:00:00	Shipping	Dock1
08/02/2020	1234	17:00:00	Shipping	Dock1

Hopefully that makes more sense?

[v-alq-msft · Accepted solution]

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]

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

[v-alq-msft]

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]

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]

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

[pedanticpad]

Hi @v-alq-msft ,

See below step before the pivot;

I tried to filter out the null values but I got the below after pivoting;

Error;

Is the issue maybe that I am trying to do this against all employees not just one like in the example I gave?