Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
eliasayyy
Super User
Super User

Find Employee wage by date

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/20221Emp A7.535
8/1/20222Emp B7.535
8/1/20223Emp C734.5
8/1/20224Emp D734.5
8/1/20225Emp E734.5
8/1/20226Emp F734.5
8/1/20227Emp G734.5
8/1/20228Emp H734.5
8/1/20229Emp I734.5
8/1/202210Emp J835.5
11/1/20221Emp A7.7535
11/1/20222Emp B7.7535
11/1/20223Emp C7.2534.5
11/1/20224Emp D7.2534.5
11/1/20225Emp E7.2534.5
11/1/20226Emp F7.2534.5
11/1/20227Emp G7.2534.5
11/1/20228Emp H7.2534.5
11/1/20229Emp I7.2534.5
11/1/202210Emp J8.2535.5
12/1/202211Emp K7.2534.5
1/1/202312Emp L7.2534.5
2/1/202313Emp M7.2534.5
5/1/20233Emp C935.5
5/1/20234Emp D835.25
5/1/20235Emp E835.25
5/1/20236Emp F7.7535.25
5/1/20237Emp G7.535
5/1/20238Emp H7.535
5/1/20239Emp I7.535
5/1/202310Emp J7.535
5/1/202311Emp K7.535
5/1/202312Emp L7.535
5/1/202313Emp M7.535
6/1/202314Emp N7.535


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   
1Emp A8/1/20221/1/2023
2Emp B8/1/20222/1/2023
3Emp C8/1/2022 
4Emp D8/1/2022 
5Emp E8/1/2022 
6Emp F8/1/20225/20/2023
7Emp G8/1/2022 
8Emp H8/1/2022 
9Emp I8/1/2022 
10Emp J8/1/2022 
11Emp K12/1/2022 
12Emp L1/1/2023 
13Emp M2/1/2023 
14Emp N6/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 

2 ACCEPTED SOLUTIONS

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_0-1692949514731.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






View solution in original post

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

View solution in original post

17 REPLIES 17
some_bih
Super User
Super User

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
 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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

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_0-1692949514731.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






no issues thank you again

Hi @eliasayyy 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






some_bih
Super User
Super User

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?

some_bih_0-1692790820314.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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



Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.