Create Card with Value by Date

[Anonymous]

hello, i have this powerbi dahsboard and all the details below:

I have 2 tables:

attendance table:

Id	Name	Date	Clock in	Clock Out	Absent
1	Lebron	6/1/2022	00:00:00	00:00	TRUE
1	Lebron	6/2/2022	07:00	15:00	Present
1	Lebron	6/3/2022	07:00	15:00	Present
1	Lebron	6/4/2022	07:00	15:00	Present
1	Lebron	7/3/2022	07:00	15:00	Present
1	Lebron	7/4/2022	07:00	15:00	Present
1	Lebron	7/5/2022	07:00	15:00	Present
1	Lebron	7/6/2022	07:00	15:00	Present
1	Lebron	8/1/2022	07:00	15:00	Present
1	Lebron	8/2/2022	07:00	15:00	Present
1	Lebron	8/3/2022	07:00	15:00	Present
1	Lebron	8/4/2022	07:00	15:00	Present
2	Ari	6/1/2022	07:00	15:00	Present
2	Ari	6/2/2022	07:00	15:00	Present
2	Ari	6/3/2022	07:00	15:00	Present
2	Ari	6/4/2022	07:00	15:00	Present
2	Ari	7/3/2022	07:00	15:00	Present
2	Ari	7/4/2022	00:00:00	00:00	TRUE
2	Ari	7/5/2022	00:00:00	00:00	TRUE
2	Ari	7/6/2022	07:00	15:00	Present
2	Ari	8/1/2022	07:00	15:00	Present
2	Ari	8/2/2022	07:00	15:00	Present
2	Ari	8/3/2022	07:00	15:00	Present
2	Ari	8/4/2022	07:00	15:00	Present

and employee table:

Id	Name	Date	Payment per hour	Total days worked
1	Lebron	6/1/2022	1	3
1	Lebron	7/1/2022	1.25	4
1	Lebron	8/1/2022	1.75	4
2	Ari	6/1/2022	2	4
2	Ari	7/1/2022	2.5	2
2	Ari	8/1/2022	2.5	4

i want to find value of payment per hour each month. 

so if i put the month of august, when i press on ari, i get 2.5, while lebron 1.75

if i put month of june, if i press on ari, o get 2, while if i press lebron i get 1

@SpartaBI @amitchandak @tamerj1 

[tamerj1 · Accepted solution]

Hi @Anonymous 
Actually the solution I provided in your previous post shall work fine with no issues. The reason is when the employee is absent then his time is already zero and no need to make any further filtering.

https://www.dropbox.com/t/l7mnejNnTkgNaqr3

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
                    && attendance[Absent] = "Present"
            ),
            VAR Clockin = attendance[Clock in]
            VAR Clockout = attendance[Clock Out]
            VAR NumberOfHours = DATEDIFF ( Clockin, Clockout, HOUR )
            RETURN
                CurrentRate * NumberOfHours
        )
)