Forum Discussion
Create Card with Value by Date
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
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 ) )
1 Reply
- tamerj1
Community Champion
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 ) )