Forum Discussion
Change payment every month
hello i have 2 tables
attendance:
| Id | Name | Date | Clock in | Clock Out |
| 1 | Lebron | 06/01/22 | 07:00 | 15:00 |
| 1 | Lebron | 07/01/22 | 07:00 | 15:00 |
| 1 | Lebron | 08/01/22 | 07:00 | 15:00 |
| 2 | Ari | 06/01/22 | 07:00 | 15:00 |
| 2 | Ari | 07/01/22 | 07:00 | 15:00 |
| 2 | Ari | 08/01/22 | 07:00 | 15:00 |
and employee table:
| Id | Name | Date | Payment per hour |
| 1 | Lebron | 06/01/22 | 1 |
| 1 | Lebron | 07/01/22 | 1.25 |
| 1 | Lebron | 08/01/22 | 1.75 |
| 2 | Ari | 06/01/22 | 2 |
| 2 | Ari | 07/01/22 | 2.5 |
| 2 | Ari | 08/01/22 | 3 |
i want to calculate pay for each employee bknowing each month the pmt per hour changes:
when i tried, i got an error because relationships are many to many so i cant use the RELATED syntax
please help
Hi Anonymous
Here is a sample file with the propsed solution https://www.dropbox.com/t/i3qSlzaPNNMgXE9DMonthly Salary Column = VAR CurrentRate = employee[Payment per hour] VAR CurrentMonth = MONTH ( employee[Date] ) VAR CurrentYear = YEAR ( employee[Date] ) RETURN SUMX ( FILTER ( RELATEDTABLE ( attendance ), MONTH ( attendance[Date] ) = CurrentMonth && YEAR ( attendance[Date] ) = CurrentYear ), VAR Clockin = attendance[Clock in] VAR Clockout = attendance[Clock Out] VAR NumberOfHours = DATEDIFF ( Clockin, Clockout, HOUR ) RETURN CurrentRate * NumberOfHours )Monthly Salary Measure = SUMX ( employee, VAR CurrentRate = employee[Payment per hour] VAR CurrentMonth = MONTH ( employee[Date] ) VAR CurrentYear = YEAR ( employee[Date] ) RETURN SUMX ( FILTER ( RELATEDTABLE ( attendance ), MONTH ( attendance[Date] ) = CurrentMonth && YEAR ( attendance[Date] ) = CurrentYear ), VAR Clockin = attendance[Clock in] VAR Clockout = attendance[Clock Out] VAR NumberOfHours = DATEDIFF ( Clockin, Clockout, HOUR ) RETURN CurrentRate * NumberOfHours ) )
3 Replies
- tamerj1
Community Champion
Hi Anonymous
Here is a sample file with the propsed solution https://www.dropbox.com/t/i3qSlzaPNNMgXE9DMonthly Salary Column = VAR CurrentRate = employee[Payment per hour] VAR CurrentMonth = MONTH ( employee[Date] ) VAR CurrentYear = YEAR ( employee[Date] ) RETURN SUMX ( FILTER ( RELATEDTABLE ( attendance ), MONTH ( attendance[Date] ) = CurrentMonth && YEAR ( attendance[Date] ) = CurrentYear ), VAR Clockin = attendance[Clock in] VAR Clockout = attendance[Clock Out] VAR NumberOfHours = DATEDIFF ( Clockin, Clockout, HOUR ) RETURN CurrentRate * NumberOfHours )Monthly Salary Measure = SUMX ( employee, VAR CurrentRate = employee[Payment per hour] VAR CurrentMonth = MONTH ( employee[Date] ) VAR CurrentYear = YEAR ( employee[Date] ) RETURN SUMX ( FILTER ( RELATEDTABLE ( attendance ), MONTH ( attendance[Date] ) = CurrentMonth && YEAR ( attendance[Date] ) = CurrentYear ), VAR Clockin = attendance[Clock in] VAR Clockout = attendance[Clock Out] VAR NumberOfHours = DATEDIFF ( Clockin, Clockout, HOUR ) RETURN CurrentRate * NumberOfHours ) ) 
vapid128Solution Specialist
add an index colnum on both table.
ID&YYMM = [ID]&"_"&FORMAT([Date],"YYMM")
And Link those 2 index colnums
- ribisht17
Super User
Anonymous
Turning the data model to
so that you can avoid Many-Many relationship (best practice)
Regards,
Ritesh
