Forum Discussion
Calculated column Table - Dates
no need to modify the DAX, update the relationship between tables.
pls see the attachment below
then pls provide the sample data that you are using for the testing. provide the data of three tables.
you can just paste the table in the reply. then let me know what's the expected output.
Excellent thank you, please see the 3 tables:
P_Employee
| Stakeholder ID | Membership_SID |
| A1 | Membership1 |
| B2 | Membership2 |
| C3 | Membership3 |
P_Payroll
| Stakeholder ID | Date |
| A1 | 2022-07-10 |
| A1 | 2022-08-06 |
| A1 | 2022-09-03 |
| A1 | 2022-10-15 |
| A1 | 2023-04-01 |
| A1 | 2023-09-19 |
| A1 | 2023-09-29 |
| B2 | 2023-05-05 |
| B2 | 2023-08-12 |
| C3 | 2022-12-15 |
| C3 | 2023-10-15 |
P_Employment
| Membership_SID | Effective | Pay Rate |
| Membership1 | 2022-07-10 | 35860.5 |
| Membership1 | 2022-10-12 | 37596 |
| Membership1 | 2023-03-25 | 40989 |
| Membership1 | 2023-09-19 | 45589.6 |
| Membership2 | 2022-10-12 | 11000 |
| Membership2 | 2023-08-10 | 18500 |
| Membership3 | 2022-12-10 | 19000 |
| Membership3 | 2023-10-01 | 23500 |
Expected output through P_Payroll:
| Stakeholder ID | Date | Calc. column |
| A1 | 2022-07-10 | 35860.5 |
| A1 | 2022-08-06 | 35860.5 |
| A1 | 2022-09-03 | 35860.5 |
| A1 | 2022-10-15 | 37596 |
| A1 | 2023-04-01 | 40989 |
| A1 | 2023-09-19 | 45589.6 |
| A1 | 2023-09-29 | 45589.6 |
| B2 | 2023-05-05 | 11000 |
| B2 | 2023-08-12 | 18500 |
| C3 | 2022-12-15 | 19000 |
| C3 | 2023-10-15 | 23500 |
Here you go Ryan, let me know if you need anything else. In short, same approach as what you provided me with, except that now we have several members captured, and was hoping to have their respective salary records carried over to the Payroll table.
P_Employee has a relationship with P_Payroll on Employee ID, P_Employee has a relationship with P_Employment on Membership_SID.
This has given me headache for the last few days, really appreciate your help.
Thanks again!
i create a measure , pls see the attachment below
Thanks Ryan, that seems to be correct, great! One question though, is it possible that it is not capturing the correct salary for the member B2 on 5/5/2023? I would have expected 11000, wouldn't it be the expected? Or maybe I'm missing something. But thanks so much
no need to modify the DAX, update the relationship between tables.
pls see the attachment below