Find Employee wage by date

[eliasayyy]

hello every one i have current dataset called employee wage

Date_Info	ID	Employee_Name	Pay _Per_Hour	Transport_Per_Day	Overtime_Pay_Per_Hour
8/1/2022	1	Emp A	7.5	3	5
8/1/2022	2	Emp B	7.5	3	5
8/1/2022	3	Emp C	7	3	4.5
8/1/2022	4	Emp D	7	3	4.5
8/1/2022	5	Emp E	7	3	4.5
8/1/2022	6	Emp F	7	3	4.5
8/1/2022	7	Emp G	7	3	4.5
8/1/2022	8	Emp H	7	3	4.5
8/1/2022	9	Emp I	7	3	4.5
8/1/2022	10	Emp J	8	3	5.5
11/1/2022	1	Emp A	7.75	3	5
11/1/2022	2	Emp B	7.75	3	5
11/1/2022	3	Emp C	7.25	3	4.5
11/1/2022	4	Emp D	7.25	3	4.5
11/1/2022	5	Emp E	7.25	3	4.5
11/1/2022	6	Emp F	7.25	3	4.5
11/1/2022	7	Emp G	7.25	3	4.5
11/1/2022	8	Emp H	7.25	3	4.5
11/1/2022	9	Emp I	7.25	3	4.5
11/1/2022	10	Emp J	8.25	3	5.5
12/1/2022	11	Emp K	7.25	3	4.5
1/1/2023	12	Emp L	7.25	3	4.5
2/1/2023	13	Emp M	7.25	3	4.5
5/1/2023	3	Emp C	9	3	5.5
5/1/2023	4	Emp D	8	3	5.25
5/1/2023	5	Emp E	8	3	5.25
5/1/2023	6	Emp F	7.75	3	5.25
5/1/2023	7	Emp G	7.5	3	5
5/1/2023	8	Emp H	7.5	3	5
5/1/2023	9	Emp I	7.5	3	5
5/1/2023	10	Emp J	7.5	3	5
5/1/2023	11	Emp K	7.5	3	5
5/1/2023	12	Emp L	7.5	3	5
5/1/2023	13	Emp M	7.5	3	5
6/1/2023	14	Emp N	7.5	3	5

and i have a calendar date from august 1 2022 to september 30 2023 , 

and i have an employee attendance table that has the punch time of each employee 

and finally i have employee table

ID	Employee_Name	Join_Date	Leave_Date
1	Emp A	8/1/2022	1/1/2023
2	Emp B	8/1/2022	2/1/2023
3	Emp C	8/1/2022
4	Emp D	8/1/2022
5	Emp E	8/1/2022
6	Emp F	8/1/2022	5/20/2023
7	Emp G	8/1/2022
8	Emp H	8/1/2022
9	Emp I	8/1/2022
10	Emp J	8/1/2022
11	Emp K	12/1/2022
12	Emp L	1/1/2023
13	Emp M	2/1/2023
14	Emp N	6/1/2023

i need to make a measure to find the wage of each employee by date

so on august 2 2022 , if i use max , i will get blank because the employee wage table doenst have august 2 2022

and if i choose after the end date of the employee , i need it to stop calculating 

i know how to do it in power query by grouping the id then merging with date table and filling down but it will create lots of rows for each employee and each date and will be very costly on performance .

any ways to do it in dax?

Excel Data 

Powerbi Report 

[some_bih · Accepted solution]

Hi @eliasayyy this was real challenge 🙂 as "increase" in pay per hour was not available per date. Compared to previous suggested cal. columns, and fact about not available pay per hours on day level, I changed possible solution in way that all following calculated columns are in table Punch Machine. This table is actual data for working hours which are input for calculation of wage at end, so basically this is mostly "master table" with Attendance.
Based on this, you can see data per employee per month as shown on picture below.

Did I answer your question? Kudos appreciated / accept solution!

Pay _Per_Hour New =

//calculation of amount pay per hour from table Employee Wage

VAR __SelectedDate = 'Punch Machine'[Date]

VAR __EmployeeID = 'Punch Machine'[ID]

VAR __FilteredRows =

    FILTER(

        ALL('Employee Wage'),

        'Employee Wage'[Employee_Name] = 'Punch Machine'[Employee_Name] &&

        'Employee Wage'[Date_Info] <= __SelectedDate

    )

VAR __LatestValidDate =

    MAXX(

        __FilteredRows,

        'Employee Wage'[Date_Info]

    )

RETURN

    MAXX(

        FILTER(

            __FilteredRows,

            'Employee Wage'[Date_Info] = __LatestValidDate

        ),

         'Employee Wage'[Pay _Per_Hour]

)

OverTime Hours Cal New =

VAR __standard_working_hours=8

VAR __total_working_hours='Punch Machine'[Working Hours]

VAR __Result=IF(__total_working_hours- __standard_working_hours>=0,__total_working_hours- __standard_working_hours,0)

RETURN __Result

Overtime _Per_Hour New =

//calculation of amount overtime per hour from table Employee Wage

VAR __SelectedDate = 'Punch Machine'[Date]

VAR __EmployeeID = 'Punch Machine'[ID]

VAR __FilteredRows =

    FILTER(

        ALL('Employee Wage'),

        'Employee Wage'[Employee_Name] = 'Punch Machine'[Employee_Name] &&

        'Employee Wage'[Date_Info] <= __SelectedDate

    )

VAR __LatestValidDate =

    MAXX(

        __FilteredRows,

        'Employee Wage'[Date_Info]

    )

RETURN

    MAXX(

        FILTER(

            __FilteredRows,

            'Employee Wage'[Date_Info] = __LatestValidDate

        ),

         'Employee Wage'[Overtime_Pay_Per_Hour]

)

Wage Cal New =

VAR __standar_working_hours=8

VAR __over_time_wh=[OverTime Hours Cal New]

VAR __result=

IF(not ISBLANK('Punch Machine'[Working Hours]),

__standar_working_hours*'Punch Machine'[Pay _Per_Hour New]+__over_time_wh*[Overtime _Per_Hour New],0

)

RETURN __result

[some_bih]

Hi @eliasayyy I understand logic of calculation "wage" with overtime.

Did I answer your question? Kudos appreciated / accept solution!

For Emp D I do not see issue as you do 🙂 due following

I only created cal. columns as data should be per date and per employee ID. 
Based on output ( I do not have access to upload file), each employee is Active meaning that wage is caclulated per that day in table Employee Wage.

Column Wage Cal is amount of wage "final" for that day and combinated with column Employee Status for Cal it should be enought to calculate your requested amount per month etc. 

In Datamodel I calculated following cal. columns

Working Hours = DATEDIFF('Punch Machine'[Clock_In],'Punch Machine'[Clock_Out],HOUR) - in Punch machine table

In table Employee Wage I inserted columns as following:

Working Hours Related = CALCULATE(

    sum('Punch Machine'[Working Hours]),

    RELATEDTABLE('Punch Machine'))+0 -- zero is not best practice, but it is working fine on small number of rows

Wage Cal =

VAR __standar_working_hours=8

VAR __over_time_wh='Employee Wage'[OverTime Hours Cal]

VAR __result=__standar_working_hours*'Employee Wage'[Pay _Per_Hour]+__over_time_wh*'Employee Wage'[Overtime_Pay_Per_Hour]

RETURN __result

Leave Date Related =

   CALCULATE(MIN('Employment Info'[Leave_Date]),

RELATEDTABLE('Employment Info'))

Employee Status for Cal =

IF(

    'Employee Wage'[Leave Date Related] >= 'Employee Wage'[Date_Info]

    || ISBLANK('Employee Wage'[Leave Date Related]),

    "Active",

    "Not Active"

)

OverTime Hours Cal =

VAR __standard_working_hours=8

VAR __total_working_hours='Employee Wage'[Working Hours Related]

VAR __Result=IF(__total_working_hours- __standard_working_hours>=0,__total_working_hours- __standard_working_hours,0)

RETURN __Result

[eliasayyy]

hello @some_bih 

thank you again for you reply 

as i see , you made calculated column for working hours in attendance table and then related this number to the wage 

however the wage is just the info of the imployee on how much they will get paid 

so to be more clear 

employee A for example 
on august 1 2022 , their basic hourly pay was 7.5$ , so for every day in punch machine , employee A should have hourly wage of 7.5$ until november 1 2022 when employee A got a raise and now earns 7.75$ an hour so before november 1 2022 , the employee A should be calculated by their total work hour x their wage which was 7.5$ now from november 1 and until the next change , until november 1 2022 when employee A got a raise and now earns 7.75$ an hour so before november 1 2022 , the employee A should be calculated by their total work hour x their new wage which is 7.75$

I hope i madde it clearer . dont hesitate to ask for more info

[some_bih]

Hi @eliasayyy Question: what is the last date according to calendar table is is 30.09.2023?

[eliasayyy]

hello @some_bih  the last date is 30 september 2023 in this scenario and yes monday to friday but i dont think its important due to attendance having 0 working hours on absent days

[some_bih · Accepted solution]

Hi @eliasayyy this was real challenge 🙂 as "increase" in pay per hour was not available per date. Compared to previous suggested cal. columns, and fact about not available pay per hours on day level, I changed possible solution in way that all following calculated columns are in table Punch Machine. This table is actual data for working hours which are input for calculation of wage at end, so basically this is mostly "master table" with Attendance.
Based on this, you can see data per employee per month as shown on picture below.

Did I answer your question? Kudos appreciated / accept solution!

Pay _Per_Hour New =

//calculation of amount pay per hour from table Employee Wage

VAR __SelectedDate = 'Punch Machine'[Date]

VAR __EmployeeID = 'Punch Machine'[ID]

VAR __FilteredRows =

    FILTER(

        ALL('Employee Wage'),

        'Employee Wage'[Employee_Name] = 'Punch Machine'[Employee_Name] &&

        'Employee Wage'[Date_Info] <= __SelectedDate

    )

VAR __LatestValidDate =

    MAXX(

        __FilteredRows,

        'Employee Wage'[Date_Info]

    )

RETURN

    MAXX(

        FILTER(

            __FilteredRows,

            'Employee Wage'[Date_Info] = __LatestValidDate

        ),

         'Employee Wage'[Pay _Per_Hour]

)

OverTime Hours Cal New =

VAR __standard_working_hours=8

VAR __total_working_hours='Punch Machine'[Working Hours]

VAR __Result=IF(__total_working_hours- __standard_working_hours>=0,__total_working_hours- __standard_working_hours,0)

RETURN __Result

Overtime _Per_Hour New =

//calculation of amount overtime per hour from table Employee Wage

VAR __SelectedDate = 'Punch Machine'[Date]

VAR __EmployeeID = 'Punch Machine'[ID]

VAR __FilteredRows =

    FILTER(

        ALL('Employee Wage'),

        'Employee Wage'[Employee_Name] = 'Punch Machine'[Employee_Name] &&

        'Employee Wage'[Date_Info] <= __SelectedDate

    )

VAR __LatestValidDate =

    MAXX(

        __FilteredRows,

        'Employee Wage'[Date_Info]

    )

RETURN

    MAXX(

        FILTER(

            __FilteredRows,

            'Employee Wage'[Date_Info] = __LatestValidDate

        ),

         'Employee Wage'[Overtime_Pay_Per_Hour]

)

Wage Cal New =

VAR __standar_working_hours=8

VAR __over_time_wh=[OverTime Hours Cal New]

VAR __result=

IF(not ISBLANK('Punch Machine'[Working Hours]),

__standar_working_hours*'Punch Machine'[Pay _Per_Hour New]+__over_time_wh*[Overtime _Per_Hour New],0

)

RETURN __result

[eliasayyy · Accepted solution]

Thats a very interesting solution thank you fir your time i did my part on power query all . First i grouped in my wage table by ID by all rows , then added an index to each row per id , then merged the leave date by id from info to wage. After that i wrote code to extract the date of index +1 only if leave date was empty. If not, the it returns leave date on this row

So i should have something like

Id  date  payperhour  index leavedate  enddate

1  8/1/2022  7.5  0 null  10/31/2022 

1 11/1/2022 7.5 1 1/1/2023 12/31/2022

Then i expanded my table and made a new column called New date which is just a list of dates between the date and end date and expanded to new rows

Now i have a full table with all dates. However i will give your solution a try because i dont want to have a huge dataset.

Thank you again

[some_bih]

Hi @eliasayyy PowerQuery with all dates could be possible solution. Thank you.

[eliasayyy]

@some_bih could you please share the powerbi file with the solution you provided?  
here is mine Payroll_Project.pbix

[some_bih]

Hi @eliasayyy sure, later tonight or during weekend (due to firewall..)

[eliasayyy]

no issues thank you again

[some_bih]

Hi @eliasayyy 

Link for file https://1drv.ms/u/s!AlrkFTmXWup1jyQZyXWqgQmK9R3B?e=DQa0aS 

[some_bih]

@eliasayyy Another question: days are regular working days: Monday to Friday or...

[some_bih]

Hi @eliasayyy I now understand more what is needed. I will take this with me and let you know.

[eliasayyy]

Thank you for your reply i will test it by tomorrow.  Before i do , my goal is to have a monthly slicer where i choose the month and see how many hours they made by day of each employee and how much they earned each day 

[some_bih]

Hi @eliasayyy do not worry about this monthly overview. Firstly, confirm plausibility of cal.columns and tomorrow we continue on that overview🙂. Have a nice rest of day

[some_bih]

Hi @eliasayyy I did not find / see logic for Wage calculation?

It should be something like Present (make sense :)) Hours *  Pay _Per_Hour and what else. In short how to calculate it?
In table Punch Machine I calulate 

Working Hours = DATEDIFF('Punch Machine'[Clock_In],'Punch Machine'[Clock_Out],HOUR)

Does it make sense to you?

[eliasayyy]

hello @some_bih thank you for your reply 

all makes sense this is correct first we find the hours worked which i know how

as you notice, there is a date every day for punch date but not for employee wage

the total working hour is clock out - clock in but if >8 then it should count frist 8 hours as normal hourly wage and the rest as overtime 

as you see what you sent me the emp D , on 2 kolovaza 2022 , they worked 9 hours so for first 8 hours , we multiply 8 * hourly wage which is 7 so 8* 7 and then 1 overtime hour is 1 * 4.5 
So my main issue is bringing this 7 and 4.5 into the equation because if i use max , on 5/1/2023 , emp D got a raise to 8$ so before 5/1/2023 it should be 7 after 5/1/2023 it should become 8