topic Re: Change payment every month in DAX Commands and Tips

Change payment every month

Anonymous — Fri, 22 Jul 2022 20:08:48 GMT

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

Re: Change payment every month

vapid128 — Fri, 22 Jul 2022 21:47:43 GMT

add an index colnum on both table.

ID&YYMM = [ID]&"_"&FORMAT([Date],"YYMM")

And Link those 2 index colnums

Re: Change payment every month

ribisht17 — Sat, 23 Jul 2022 16:16:27 GMT

Anonymous

Turning the data model to

so that you can avoid Many-Many relationship (best practice)

Regards,

Ritesh

Re: Change payment every month

tamerj1 — Sat, 23 Jul 2022 19:38:57 GMT

Hi Anonymous
Here is a sample file with the propsed solution https://www.dropbox.com/t/i3qSlzaPNNMgXE9D

Monthly 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 ) )