Change payment every month

[eliasayy]

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

[tamerj1 · Accepted solution]

Hi @eliasayy 
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
        )
)

[ribisht17]

@eliasayy 

Turning the data model to 

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

Regards,

Ritesh

[vapid128]

add an index colnum on both table.

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

And Link those 2 index colnums